How do I join a parent table to two child tables without duplication? parent table




id
city_name
zip_code




1
new york city
10001


2
los angeles
90001


3
chicago
60007




Customer, child table




id
customer_name
city_id
address




1
alice
1
428 Hartford Lane


2
betty
1
249 George Street


3
cath
3
12112 S Front Ave


4
daph
3
8619 S Wolcott Ave




Restaurant, child table




id
address
city_id




1
256 Roehampton St.
1


2
920  Cherry Camp Road
3


3
7609 Mckinley Ave
2


4
5723 Morgan Ave
2




When using the following query,
    select c.id, c.city_name, c.zip_code, r.id, r.address, r.city_id, cus.id, cus.customer_name, cus.city_id, cus.address 
    from restaurant r 
    left join city c 
        on r.city_id = c.id 
     right join customer cus 
        on c.id = cus.city_id where c.id = 1;

There will be a duplicate restaurant address entry. How do I ensure that the restaurants and customers are distinct?




id
city_name
zip_code
id
address
city_id
id
customer_name
city_id
address




1
new york city
10001
1
256 Roehampton St.
1
1
alice
1
428 Hartford Lane


1
new york city
10001
1
256 Roehampton St.
1
2
betty
1
249 George Street




***Edit: To add some context, I would like to return a json object of a city which contains its fields as well as a list of customers and a list of restaurants belonging to that particular city.
Is it more efficient to make three separate queries to populate that object, i.e.
select * from city where id = 1;
select * from customer where city_id = 1;
select * from restaurant where city_id = 1;

or
perform one query with two joins, with the expected result as the following, where there are no duplicate restaurants or customers




id
city_name
zip_code
id
address
city_id
id
customer_name
city_id
address




1
new york city
10001
1
256 Roehampton St.
1
1
alice
1
428 Hartford Lane


1
new york city
10001



2
betty
1
249 George Street




Or can I just use a distinct keyword to remove duplicates?
What you're looking to do is denormalize your data into a JSON object. This is usually not a standard thing to do from a relational database, but PostgreSQL has a cool function that makes it possible called row_to_json(). You should be able to distinctify your JSON object using that function accordingly on top of your query's result set.