Sample Header Ad - 728x90

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

0 votes
2 answers
907 views
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?
Asked by 12johnny (3 rep)
Feb 9, 2021, 12:20 AM
Last activity: Jan 25, 2025, 01:03 PM