0

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?

5
  • How do you want the end results to look like, please provide an example?
    – J.D.
    Feb 9 at 0:48
  • If you join on a non-unique column, you can get multiple rows. You have two people who live in New York, how do you want them to be displayed? Feb 9 at 1:20
  • @J.D. have added additional info
    – 12johnny
    Feb 9 at 1:47
  • @Charlieface have added additional info
    – 12johnny
    Feb 9 at 1:47
  • Well... you could use arrays, but I'm far from saying that is a best practice. Something like this: dbfiddle.uk/…
    – Sotis
    Feb 15 at 19:32
0

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.

2
  • ohh interesting, did not know such a function existed. However, I am using golang's built in package to execute queries and create the corresponding objects and json results
    – 12johnny
    Feb 9 at 4:16
  • As jsonb is recommended over json, the proper function would be to_jsonb() rather than row_to_json() Feb 9 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.