Saturday, March 3, 2012

Another join issue - keeping the outer join chain

As a follow up for the last post another way to mess up outer joins is by joining an inner table after the left join
Regarder:
select a.name, c.name as item , sum(b.sale_amount) amount
from
(select 1 id ,'herby'  name
union  select 2 id ,'Boris'  name
union select 3 id ,'tony'  name)
a
left join
(select 1 id , 34  sale_amount,1 item_id
union  select 2 id ,344  sale_amount, 2
union select 2 id ,14  sale_amount,3 )
b
on a.id = b.id
left join
(select 1 id , 'ajax' name
union  select 2 id ,'beans'
)
c
on b.item_id = c.id
group by a.name,c.name
order by 1,2,3
produces

while
select a.name, c.name as item , sum(b.sale_amount) amount
from
(select 1 id ,'herby'  name
union  select 2 id ,'Boris'  name
union select 3 id ,'tony'  name)
a
left join
(select 1 id , 34  sale_amount,1 item_id
union  select 2 id ,344  sale_amount, 2
union select 2 id ,14  sale_amount,3 )
b
on a.id = b.id
inner  join 
(select 1 id , 'ajax' name
union  select 2 id ,'beans'
)
c
on b.item_id = c.id
group by a.name,c.name
order by 1,2,3
produces

No comments:

Post a Comment