Multiple join Query

[atiqraza]

Could anyone tell me what i am doing wrong here,
I want to do join three tables together,
I have one master table and i am performing a left outer join on it with two tables.

select * from custmast a left outer join chainmlrc b, chainbanner c on
a.country = b.country and
a.can_prim_chain_code = b.[chain ID] and
a.can_prim_chain_code = c.[chain ID] and
a.can_prim_region_code =c.[banner ID]

Is this possible or do i have to create a view for the first join and then join it with the second table.

 

[gmmastros]

Your syntax is wrong. It should be like this...

select * 
from   custmast a 
       left outer join chainmlrc b
         a.country = b.country and 
         a.can_prim_chain_code = b.[chain ID]
       left join chainbanner c on 
         a.can_prim_chain_code = c.[chain ID] and
         a.can_prim_region_code =c.[banner ID]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

You have to do 2 separate joins on these tables it would look like this:

select  * from custmast a 
left outer join chainmlrc b
   on a.can_prim_chain_code = b.[chain ID] and a.country = b.country 
left outer join chainbanner c on 
   b.[chain_id] = c.[chain ID] and a.can_prim_region_code = c.[banner ID]

 

[atiqraza]

Hey thnx it worked!!
I knew i was doing something stupid....

 

[gmmastros]

>> I knew i was doing something stupid....

Actually, I think you just did something smart. You learned the proper way to join tables.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

wow this guy is good!! smoove cat