asked on

sql - inner join, right join and outer join

I have following tables in sql server. An employee can belong to multiple departments. if i want to find out an employee that belongs to a department say HR and sales by joining employee table and EmployeeDepartment junction table, what kind of join I should use.

employee table :
Id Name
1 sam
2 joe

Department table :
id Name
1 HR
2 Sales

EmployeeDeparment Junction table
Id Eid Did
1 1 1
2 1 2

Lokesh B R

Hi,

SELECT E.Id, E.Name FROM Employee E
INNER JOIN EmployeeDepartment ED ON E.Id = ED.EId
INNER JOIN Department D ON ED.DId = D.ID
WHERE D.Name = 'HR' AND D.Name = 'SALES'

Open in new window

Nitin Sontakke

I guess, that would be:

select e.Id, e.Name
from employee e
left outer join employeeDepartment ed on ed.eId = e.Id
left outer join department d on ed.dId = d.Id
where d.Name in ('HR', 'Sales') -- This is important as AND will not satisfy the condition.

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

>sql - inner join, right join and outer join
Since the above experts are on the right track with the T-SQL answer, I'll respond to the question title with 'Do a images.google.com search for 'SQL Join', and you'll see lots of pretty Venn diagrams about how the JOINs work and the T-SQL needed to pull them off. Copyright rules prevent us from just copy-pasting one into this question.'

mikha

ASKER

Thank you all for your answers.

@PortletPaul - thanks for the good solution and explanation. I have to support "OR" search too. say, employee that belongs to HR or sales. can this also be achieved with inner join of EmployeeDeparment and Employee table?

PortletPaul

For an OR condition you an use the first answer above, just replacing AND with OR
alternatively you could use IN() as shown in the second answer above, but outer joins are not required

--based on query by: Lokesh B R
SELECT E.Id, E.Name FROM Employee E
INNER JOIN EmployeeDepartment ED ON E.Id = ED.EId
INNER JOIN Department D ON ED.DId = D.ID
WHERE D.Name = 'HR' OR D.Name = 'SALES'

The second answer is OR based already:

-- based on query by: Nitin Sontakke
select e.Id, e.Name
from employee e
inner join employeeDepartment ed on ed.eId = e.Id
inner join department d on ed.dId = d.Id
where d.Name in ('HR', 'Sales') -- "equates to 1 OR 2"

mikha

ASKER

@PortletPaul - thanks,

but as you mentioned without the group by clause, wouldn't a search for employee that belongs to both HR and Sales return two rows of sam (from my original example) in the result. . since he belongs to both departments.

so i still need group by to, just get one record of sam.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikha

ASKER

@portletPaul - thank you again for your insight. one alternative for the OR search, i thought of was - if we were passing in group of Dids

select e.*
from employee as e
inner join (
select eid
from EmployeeDeparment
where did in @groupDid
group by eid
) as d
on e.id = d.eid