lundi 21 mai 2018

SQL If Not Any From Left Join Else Left Join

I the following query and I only want the results from the first left join that returns results for the addresses (add1/2/3), phones (phone1/2/3), and email_addresses(email1/2/3). Is it possible, or should I just return it all and filer the results later?

select 
mc.counsel_id as party_id,
coalesce(c.attorney_id, c.name_id) as name_id,
coalesce(c.name_id, c.attorney_id) as wrt_id,
n.quick_name_f as display_name,
wrtn.quick_name_f as with_respect_to_name,
n.delivery_channel_id as deliver_channel_id1,
wrtn.delivery_channel_id as deliver_channel_id2,
-- I want to select the first left join that had results for
-- addresses, phones, and email_addresses
from names n, names wrtn, matter_counsel mc, counsel c
left join(
    select name_id, with_respect_to_id, address_type, display_address
        from addresses
) as add1 on add1.name_id = c.attorney_id and 
            add1.with_respect_to_id = c.name_id
left join(
    select name_id, with_respect_to_id, address_type, display_address
        from addresses
        where name_id = with_respect_to_id
) as add2 on add2.name_id = c.name_id
left join(
    select name_id, with_respect_to_id, address_type, display_address
        from addresses
        where name_id = with_respect_to_id
) as add3 on add3.name_id = c.attorney_id
left join(
    select name_id, with_respect_to_id, phone_type, disp_phone
        from phones
) as phone1 on phone1.name_id = c.attorney_id and 
            phone1.with_respect_to_id = c.name_id
left join(
    select name_id, with_respect_to_id, phone_type, disp_phone
        from phones
        where name_id = with_respect_to_id
) as phone2 on phone2.name_id = c.name_id
left join(
    select name_id, with_respect_to_id, phone_type, disp_phone
        from phones
        where name_id = with_respect_to_id
) as phone3 on phone3.name_id = c.attorney_id
left join(
    select name_id, with_respect_to_id, email_address_type, email_address
        from email_addresses
) as email1 on email1.name_id = c.attorney_id and 
            email1.with_respect_to_id = c.name_id
left join(
    select name_id, with_respect_to_id, email_address_type, email_address
        from email_addresses
        where name_id = with_respect_to_id
) as email2 on email2.name_id = c.name_id
left join(
    select name_id, with_respect_to_id, email_address_type, email_address
        from email_addresses
        where name_id = with_respect_to_id
) as email3 on email3.name_id = c.attorney_id
where mc.matter_id = 538996 and 
    mc.end_date is null and 
    c.counsel_id = mc.counsel_id and 
    wrtn.name_id = coalesce(c.name_id, c.attorney_id) and
    n.name_id = coalesce(c.attorney_id, c.name_id) and
    exists (select * 
        from party_counsel pc, matter_parties mp, 
                parties p, party_types pt 
        where pc.counsel_id = mc.counsel_id and 
                mp.matter_id = mc.matter_id and 
                mp.party_id = pc.party_id and 
                mp.end_date is null and 
                p.party_id = mp.party_id and 
                pt.party_type_id = p.party_type_id)
order by n.quick_name_l

Thanks in advanced.

Aucun commentaire:

Enregistrer un commentaire