title: SQL query: Fetch Employee Name (i.e. FirstName space LastName), Department Name, Manager, City, StateProvinceCode, CountryRegionCode
body: <p>CONDITIONS:  Where city is 'New York'. If Manager is NULL then display 'CEO'.
  I have shared the link of table structure.</p>
  
  <p><img src="https://i.stack.imgur.com/exJ8P.jpg" alt="Table Structure/Schema Diagram"></p>
  
  <p>I understand that I may be able to achieve this via sub-queries, CTE or a Self Join. But I am new to CTEs/Self Joins so need some help. </p>
  
  <p>Trying something like below: But I am very confused how to do this.</p>
  
  <pre><code>WITH CTE EmployeeManager(
  
  Select Concat(FirstName, ,LastName) as [Employee Name] 
  FROM Contact C JOIN Employee E ON C.ContactID=E.ContactID
  Where E.ManagerID IS NULL
  UNION ALL
  Select Concat(FirstName, ,LastName) as [Employee Name] 
  FROM Contact C JOIN Employee E ON C.ContactID=E.ContactID
  JOIN EmployeeManager EM ON E.ManagerID=EM.EmployeeID 
  )
  
  Select [Employee Name]  from EmployeeManager;
  </code></pre>
  
  <p>Not able to proceed further. Please help...</p>