mysql - 努力理解复杂的联接

下面是我的问题

SELECT cover.id              AS cID,
       cover.title           AS cTitle,
       cover.slug            AS cSlug,
       cover.image           AS cImage,
       cover.timestamp       AS cTimestamp,
       category.name         AS catName,
       category.slug         AS catSlug,
       GROUP_CONCAT(tag.tag) AS tags
FROM   cover
       JOIN category
         ON cover.category_id = category.id
       LEFT OUTER JOIN cover_tag
         ON cover_tag.cover_id = cover.id
       LEFT OUTER JOIN tag
         ON tag.id = cover_tag.tag_id
GROUP  BY cover.title
LIMIT  0, 30

这将返回所有封面和每个封面的所有标签。现在，如果我只想返回有“cars”标签但仍然返回封面上所有标签的行，该怎么办？
我试图在谷歌上找到帮助，但不知道我在搜索什么。

最佳答案

如果我明白你的要求，

SELECT cover.id              AS cID,
       cover.title           AS cTitle,
       cover.slug            AS cSlug,
       cover.image           AS cImage,
       cover.timestamp       AS cTimestamp,
       category.name         AS catName,
       category.slug         AS catSlug,
       GROUP_CONCAT(tag.tag) AS tags
FROM   cover
       JOIN category
         ON cover.category_id = category.id
       JOIN cover_tag
         ON cover_tag.cover_id = cover.id
       LEFT OUTER JOIN tag
         ON tag.id = cover_tag.tag_id
       JOIN tag T2
         ON T2.id = cover_tag.tag_id AND T2.tag = 'cars'
GROUP  BY cover.title
LIMIT  0, 30

最后一个连接应该确保cover_标记有一个名为“cars”的标记。
编辑嗯，更好的解释。。。一张照片？他们说，节省了9000多字。封面上有一组“照片，汽车，太棒了”标签的匹配结构是：

                     [cover] ---- [category]
                        |
                        |
                        |
[tag:photos] ----/ [cover_tag] ---- [T2:cars]
[tag:cars]   ---/
[tag:great]  --/

如果封面上有一组“小丑、鞋子、气球、孩子们爱它”的标签，T2将不匹配，因此cover_tag将不匹配，因此cover将不匹配。

关于mysql - 努力理解复杂的联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8660745/