0

I have a table, say, Person with fields

id  name
1    Jone
2    Jane

Got another table address

id   person_id   address_type
1    1           Permanent
2    1           Temporary

I want to select table Person to Address such that if a person has Temporary address then join it else join with Parmanent address.

I can do:

    select * 
    from person p 
    left join address at on at.person_id=p.id and at.type='Temporary' 
    left join address al on al.person_id=p.id and at.type='Permanent'

But joining address table twice seems like overkill here. Can I just join address only once such that I get:

-> If Temporary is there, join with Temporary
-> If temporary is not there, join with permanent
-> If pernamenet is not there join address with all null fields.

Help?

  • Join address table by something like on at.person_id=p.id and at.type=MAX(at.type) OVER (PARTITION BY at.person_id). – Akina Sep 28 '18 at 7:33
  • Do you have three types, Permanent, Temporary and Local ? – Gerard H. Pille Sep 28 '18 at 8:16
  • @GerardH.Pille typo. – Sachin Verma Sep 28 '18 at 8:18
  • It happens to the best. – Gerard H. Pille Sep 28 '18 at 8:19
3

This could be done using a left join to a derived table that picks the correct address:

select p.*, a.id as address_id, a.address_type
from person p
  left join (
     select ad.*, 
            row_number() over (partition by ad.person_id 
                               order by case when ad.address_type = 'Temporary' then 1 else 2 end) as rn
     from address ad
  ) a on a.person_id = p.id and a.rn = 1;

The case expression for the order by inside the window function could also be written as decode(ad.address_type, 'Permanent', 1, 2), but I prefer standard SQL over Oracle specific one if both are equivalent.


Another option - that might or might not be faster - is to use a lateral join with a fetch first clause.

select p.*, a.id as address_id, a.address_type
from person p
  left join lateral (
     select *
     from address ad
     where ad.person_id = p.id
     order by case when ad.address_type = 'Temporary' then 1 else 2 end
     fetch first 1 rows only
  ) a on 1 = 1;

The lateral left join returns one row for each person_id from the outer query (because of the fetch first 1 rows only)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.