I am experiencing an interesting problem with a NULL value in the following table:
CREATE TABLE dbo.STAFF ( staffID int PRIMARY KEY NOT NULL, lastname varchar(50) NULL, firstname varchar(50) NULL, pay money NULL, supervisorID int NULL ) INSERT INTO STAFF (staffID, lastname, firstname, pay, supervisorID) VALUES (1, 'Smith', 'John', 60000, NULL), (2,'Jones', 'Bridget', 13000, 1), (3,'Robinson', 'Smokey', 14000, 1), (4, 'Vedder', 'Eddie', 13000, 3), (5, 'Almighty', 'Bruce', 12000, 2), (6, 'Addington', 'Jane', 11000, 2), (7, 'Hogan', 'Hulk', 10000, 3), (8, 'Jackson', 'Jacky', 15000, 2), (9, 'Samuelson', 'Sammy', 14400, 1) ;
When I run this query the first row isnt returned and it seems it is being ignored because of the null value. Why does this not work. I assume it is something to do with the NULL value in a CASE statement within a self-join? I am really interested as to why this wont work.
SELECT (a.firstname + ' ' + a.lastname) AS "Employee Name", CASE WHEN a.supervisorID IS NOT NULL THEN (b.firstname + ' ' + b.lastname) ELSE 'No supervisor' END AS "supervisor Name" FROM dbo.STAFF a, dbo.STAFF b WHERE a supervisorID= b.staffID;
You have to use a LEFT JOIN. I think if join type isn't give INNER JOIN is default.