Could you not use a simple case statement.
END AS address
FROM p_ppl p
JOIN p_adr a
ON p.id = a.id_ppl
LEFT OUTER JOIN p_adr h
ON a.id_address = h.id_Address
AND h.type_address = 'H'
Obviously expand the case statement and joins for the different scenarios you wish to cover.
Hi, thank you for the solution.
But is this the best way of doing it?
Let's say that I have many more values in the type_address column, maybe 10. So basically I should do a new join (10 joins) for every distinct value?
Thank you again.
I am not entirely sure if your desired output - Another alternative would be if you are trying to select for a range of ids in p_ppl table possibly and for each row get the correspons 'C' address if exists else get 'H'...etc
You could rank the addreses in the order you want them output by using a derived column based on case statment and then select where rownum = 1