I have three tables person, his cars, his houses. And i need select persons with his cars and houses if one of childs(car, house) table have reference to parent(person).
I tried with join, but don’t know how use OR in this condition.
Table person id name 1 Mark 2 David 3 Mike 4 Andrew Table house id city person 1 Moscow 1 2 Chicago 1 3 New York 2 4 Boston 2 Table car id brand person 1 bmw 4 2 opel 4 3 toyota 2 4 volvo 2
and result should be
name city car Mark Moscow Chicago David New York Boston toyota volvo Andrew bmw opel
Advertisement
Answer
You can left join
twice and ensure that one of the joins succeded. The rest is aggregation:
select p.name, string_agg(distinct h.city, ' ' order by h.city) cities, string_agg(distinct c.brand, ' ' order by c.brand) brands from person p left join house h on h.person = p.id left join car c on c.person = p.id where c.person is not null or h.person is not null group by p.id, p.name
name | cities | brands :----- | :-------------- | :----------- Mark | Chicago Moscow | null David | Boston New York | toyota volvo Andrew | null | bmw opel