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

Posted by Nav234 under Sql Server on 9/9/2010 | Points: 10 | Views : 6636 | Status : [Member] | Replies : 4
Hi all,

I have two tables
department table has : - departmentid,departmentname fields
employee table has : - id,name,departmentid fields

1.)Query for what are methods available to find employee not having any department using two tables?
2.)Query to find employee having department ?

Thanks in advance

S.Naveen...


Responses

Posted by: PandianS on: 9/9/2010 [Member] [MVP] Silver | Points: 50

Up
0
Down

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

Posted by: T.saravanan on: 9/9/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Nav234 on: 9/9/2010 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Nav234 on: 9/9/2010 [Member] Starter | Points: 25

Up
0
Down
thanks pandian

S.Naveen...

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

Login to post response