Postgresql – How to join a parent table to two child tables without duplication

join;postgresqlquery

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?

Best Answer

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.

Related Question