2

I need to build a query with multiple JOIN, to be more especific, 2 JOINS, but it gets duplicated results, check this:

My current tables:

food_shops

id, slug, name

categories_food_shops

id, id_cat, id_food_shop

pictures_food_shops

id, pic_slug, id_food_shop

And I need to get * from food_shops, the id_cat from categories_food_shops and pic_slug from pictures_food_shops...

My current query is like this:

SELECT food_shops.id, food_shops.slug, food_shops.name, 
GROUP_CONCAT(categories_food_shops.id_category) as categories, 
GROUP_CONCAT(pictures_food_shops.slug) as pictures 
FROM
food_shops 
JOIN categories_food_shops 
    ON food_shops.id = categories_food_shops.id_food_shop 
JOIN pictures_food_shops 
    ON food_shops.id = pictures_food_shops.id_food_shop 
GROUP BY food_shops.slug, pictures_food_shops.id_food_shop

But since my pictures_food_shops have more results as the categories_food_shops, my result is gettin "quadruplicated":

enter image description here

What can I do to prevent this and get only the correct amount of categories? Only 1 at the first row, 3 and 5 in the second one and 7,1,6 at the last one?

Thanks!

user3159043
  • 197
  • 1
  • 1
  • 16
  • Possible duplicate of [mysql GROUP\_CONCAT duplicates](http://stackoverflow.com/questions/4561650/mysql-group-concat-duplicates) – EoinS Aug 07 '16 at 18:31
  • Just to clarify where is id_category coming from? I dont see it in your schema above? – EoinS Aug 07 '16 at 18:32

1 Answers1

0

You can try this:

...
GROUP_CONCAT(DISTINCT categories_food_shops.id_category) as categories, 
....

This should work for pictures also.

Here is the documentation with DISTINCT usage example.

EoinS
  • 5,405
  • 1
  • 19
  • 32