Skip to content
Advertisement

Select data from three tables only if one of them has reference to parent table

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

Demo on DB Fiddle:

name   | cities          | brands      
:----- | :-------------- | :-----------
Mark   | Chicago Moscow  | null        
David  | Boston New York | toyota volvo
Andrew | null            | bmw opel    
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement