Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Return a row with null values

Author  Topic 

Notserp56
Starting Member

1 Post

Posted - 2014-04-19 : 11:14:16
I am trying to return all the names of employees and their managers

this query returns all the employees except for 1

SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E,EMPLOYEE M
WHERE E.SUPERSSN=M.SSN

the one that isn't returned has a null SUPERSSN, but when I add in:

OR E.SUPERSSN IS NULL

it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)

How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns? This is my first post, so pls let me know if I omitted some crucial info. TY for any help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 12:41:14
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E,EMPLOYEE M
WHERE E.SUPERSSN=M.SSN
union all
SELECT E.FNAME, E.LNAME, null, null
FROM EMPLOYEE E
WHERE E.SUPERSSN is null

or depending on your data probably
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E
left join EMPLOYEE M
on E.SUPERSSN=M.SSN


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -