left join

msrnivas · October 15th, 2004, 05:17 AM

Hi
I have 2 tables say tabA(col1,col2,col3,AdminID,cotactorid ), tabB(id,RefName)
tabA contains 5 cols out of these 5 cols ,2 cols(AdminID,cotactorid) are reffering the id field of tab B

in my sql query i want to fetch the AdminID,cotactorid and coresponding names of these 2 from tabB
my below sql giving correct refname if both adminid and contactorid are same if not its displaying null for refname.

like if adminid and contactorid are 1 its displaying
1,"Somu",1
if adminid =1 and contactorid are 2 its displaying
1,null,2 but here i want 1,"somu",2,"ramu"
how to do it.

select tabA.AdminID,tabB.RefName ,cotactorid
FROM tabA left JOIN tabB ON tabA.AdminID=tabB.idand cotactorid=tabB.id

bmains · October 15th, 2004, 07:12 AM

What table does contactorid come from?

pgtips · October 15th, 2004, 07:37 AM

sounds like you need to join tabA to tabB twice, once on each id field, like this:

Code:

select tabA.AdminID, b1.RefName AS AdminName, tabA.cotactorid, b2.RefName AS cotactorName
FROM tabA left JOIN tabB b1 ON tabA.AdminID=b1.id 
          LEFT JOIN tabB b2 ON tabA.cotactorid=b2.id