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.