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
Last activity: Jan 25, 2025, 01:03 PM