Search code examples
sqlnorthwind

Northwind database | SQL query that returns employees and who they report to

I am trying to write a query for the Northwind database that lists the employees and their manager's names, without including employees who have no one to report to.

Here is what I have so far:

SELECT employees.firstname, employees.lastname, superior.firstname, superior.lastname
FROM employees 
LEFT OUTER JOIN employees superior ON employees.reportsto = superior.employeeID 
WHERE employees.reportsto <> null;

This query runs, but nothing appears.

Solution

  • You should try:

    SELECT employees.firstname, employees.lastname, superior.firstname, superior.lastname
FROM employees 
LEFT OUTER JOIN employees superior ON employees.reportsto = superior.employeeID 
WHERE employees.reportsto IS NOT NULL --or "<> NULL" when ANSI_NULLS is set to OFF ("!=" is specific to SQL server)

    If you are using sql server, the default is to set ANSI_NULLS ON, you need to use IS/IS NOT to compare with NULL