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