CODEWITHSUNDEEP
sqloracle-database
raghu ram
raghu ram

Reputation: 27

joins on four tables

Consider a small example i have the below tables where it looks like this 

Employee(eid(pkey),ename)
supply(sid(pkey),sname,eid(fkey))
supplier(suid(pkey),supname,sid(fkey))
item(iid(pkey),itemname,suid(fkey))

help in sql joins so the output be below format

i need to display all eids and ename even though they dont have item name related to them 

eid , ename ,  itemname

to get item name

Upvotes: 0

Views: 71

Answers (1)

LukStorms
LukStorms

Reputation: 29647

Using left joins, and starting from employee, even the employees without items will be selected. 

select 
 e.eid, 
 e.ename,
 i.itemname
from employee e
left join supply s on s.eid = e.eid
left join supplier su on su.sid = s.sid
left join item i on i.suid = su.suid

Without the requirement to also select employees without items, it's better to start joining from item:

select 
 i.itemname,
 s.supname as suppliername,
 su.sname as supplyname,
 e.eid as empid, 
 e.ename as empname
from item i
left join supplier su on su.suid = i.suid
left join supply s on s.sid = su.sid
left join employee e on e.eid = s.eid

Upvotes: 1

Related Questions