CODEWITHSUNDEEP
mysqlsqlduplicates
JohnA
JohnA

Reputation: 1913

MySql request returns duplicates on LEFT JOIN

3 tables

table: people 

id name 
1  john  
2  Mike
3  Sam  
4  Andy

-

table goods:

   g_id g_name  g_class
    1   sugar  food
    2   salt   food
    3   boat   transp
    4   house  habitation
    5   car    transp

-

   table goods_owned:
      go_id person_id   good_id
        1   3           3
        2   3           1
        3   3           5
        4   2           4
        5   1           2

Now when i select 

SELCT people.id, people.name, goods.name
   FROM people
      LEFT JOIN goods_owned ON goods_owned.person_id = people.id
      LEFT JOIN goods ON goods.g_id = goods_owned.good_id
         WHERE people.id = 3

Then in response it duplicates entry's from table "people":

1, Mike, boat
1, Mike, sugar
1, Mike, car

Is there way to build request to get:

1, Mike, boat, sugar, car

Upvotes: 1

Views: 210

Answers (1)

mathematical.coffee
mathematical.coffee

Reputation: 56915

Yes, look at GROUP_CONCAT, which collects all the values for each GROUP BY and comma-separates them.

SELCT people.id, people.name, GROUP_CONCAT(goods.name)
FROM people
LEFT JOIN goods_owned ON goods_owned.person_id = people.id
LEFT JOIN goods ON goods.g_id = goods_owned.good_id
WHERE people.id = 3
GROUP BY people.id

Upvotes: 4

Related Questions