merge two left join on the same table into one

advertisements

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


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