How to find employee without any department in two table [Resolved]

Hi

1. To findout the Employees those who having Department

SELECT e.Id,e.Name FROM employee e(NOLOCK) JOIN department D(NOLOCK)

ON (e.departmentid = d.departmentid)

--(OR)

SELECT e.Id,e.Name FROM employee e(NOLOCK) 

WHERE EXISTS(SELECT 1 FROM department D(NOLOCK)

WHERE d.departmentid = e.departmentid)

--(OR)

SELECT e.Id,e.Name FROM employee e(NOLOCK) 

WHERE e.departmentid in(SELECT departmentid FROM department D(NOLOCK))

--(OR)

SELECT e.Id,e.Name FROM employee e(NOLOCK), department D(NOLOCK)

WHERE e.departmentid = d.departmentid

2. To findout the Employees those who are NOT having Department,
But if you have not forced Referential Integrity in Employee table based on Department table then it should work. If you have forced referential integrity between these tables then, This scenario will always fail...

SELECT e.Id,e.Name FROM employee e(NOLOCK) LEFT JOIN department D(NOLOCK)

ON (e.departmentid = d.departmentid)

WHERE d.departmentid IS NULL

--(OR)

SELECT e.Id,e.Name FROM employee e(NOLOCK) 

WHERE NOT EXISTS(SELECT 1 FROM department D(NOLOCK)

WHERE d.departmentid = e.departmentid)

--(OR)

SELECT e.Id,e.Name FROM employee e(NOLOCK) 

WHERE e.departmentid NOT in(SELECT departmentid FROM department D(NOLOCK))

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Hi Nav234,

1)In your employee table department id comes null or Zero(0).
2)which columns are shown in your output table?

Thanks :)

Thanks,
T.Saravanan

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

actually i am really sorry i forgot the exact question,so i have posted whatever i remember.
i think they asked little tricky like department id could be null also,so we simply cant
put query like
(select name from employee where departmentid = "" ).
but interviewer didn't wanted this.because when departmentid could be null in department table.
I remember one thing ,interviewer asked me like "how to use these both tables and get output either using any joints or something else".
OUTPUT : -"get employees not having department ?".
so can u help on this issue what i am trying to say.

S.Naveen...

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

thanks pandian

S.Naveen...

Nav234, if this helps please login to Mark As Answer. | Alert Moderator