Fredou Fredou - 7 months ago 13
SQL Question

merging two left join on same table into one

Is it possible to merge these 2 left join into one?

I can't think of any way

select left1.field1,
left2.field2
from masterTable left join (
select somefield,
field1,
row_number() over (partition by somefield orderby otherfield) as rowNum
from childTable
inner join masterTable
on masterTable.somefield = childTable.somefield
) as left1
on masterTable.somefield = left1.somefield
AND left1.rownum =1
left join (
select somefield,
max(field2) as field2
from childTable
inner join masterTable
on masterTable.somefield = childTable.somefield
where field3 = 1
group by somefield
) as left2
on masterTable.somefield = left2.somefield

vkp vkp
Answer

You can use max() over() to get the max of field2 per somefield in the same query.

select left1.field1,
       left1.field2
from masterTable 
left join
(select 
 somefield,field1 
,row_number() over (partition by somefield orderby otherfield) as rowNum
,max(field2) over(partition by somefield) as field2
from childTable 
inner join masterTable on masterTable.somefield = childTable.somefield) as left1
ON masterTable.somefield = left1.somefield 
AND left1.rownum =1 AND field3 = 1