CODEWITHSUNDEEP
sqlnorthwind
Help pls
Help pls

Reputation: 15

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.

Upvotes: 0

Views: 4427

Answers (4)

Tarun Kumar
Tarun Kumar

Reputation: 1

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

Upvotes: 0

Randi Ratnayake
Randi Ratnayake

Reputation: 684

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

Upvotes: 0

Help pls
Help pls

Reputation: 15

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

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

Upvotes: 0

LONG
LONG

Reputation: 4620

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

Upvotes: 0

Related Questions