mysql - 我怎样才能做这个多重 JOIN 查询？

Question

我需要构建一个包含多个 JOIN 的查询，更具体地说，是 2 个 JOINS，但它会得到重复的结果，检查一下:

我当前的表:

食品店

id, slug, name

类别_食品_商店

id, id_cat, id_food_shop

图片_食品_商店

id, pic_slug, id_food_shop

我需要从 food_shops 获取 *，从 categories_food_shops 获取 id_cat，从 pictures_food_shops 获取 pic_slug...

我当前的查询是这样的:

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

但是因为我的 pictures_food_shops 比 categories_food_shops 有更多的结果，所以我的结果是“四倍”:

我该怎么做才能避免这种情况并只获得正确数量的类别？第一行只有1，第二行只有3和5，最后一行只有7、1、6？

谢谢!

Answer 1

你可以试试这个:

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

这也适用于图片。

Here is the documentation 带有 DISTINCT 用法示例。