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.

4 Answers
4

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

sql server

set ANSI_NULLS

IS/IS NOT

NULL

I got it, never ever ever try to compare a value to null. The proper answer is:

WHERE employeesAM.reportsto **is not** null;


=

<>

!=

NULL

SET ANSI_NULLS

Try IS NOT NULL:

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;


Explanation:
NULL has no value, and so cannot be compared using the scalar value operators.
https://stackoverflow.com/a/5658472/684030

SELECT empa.employeeid,
empa.firstname,
empa.lastname,
empb.firstname AS 'Reports to Manager'
FROM employees empa
INNER JOIN employees empb
ON empa.reportsto = empb.employeeid


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.