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

Question

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

Answer 1

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