MySQL - 对连接表进行 AND 查询

Question

我有一个很长的 SQL 查询:

SELECT *, content.contentID AS thisID
FROM content
LEFT JOIN link_keywords ON content.contentID = link_keywords.contentID
LEFT JOIN link_countries ON content.contentID = link_countries.contentID
LEFT JOIN link_sections ON content.contentID = link_sections.contentID
WHERE status = '1' AND typeID = '1'
GROUP BY contentID
ORDER BY creationDate DESC

我返回一组结果，我想要做的是显示 sectionID (来自 link_sections 连接)等于 3 或 4 的所有结果。换句话说，第 3 部分和第 4 部分中的所有结果。

以下查询不会返回任何内容，大概是因为它正在检查 sectionID 等于 3 和 4 的单行。我需要的是任何 content.contentID ，sectionID 等于 3 和 4。

SELECT *, content.contentID AS thisID
FROM content
LEFT JOIN link_keywords ON content.contentID = link_keywords.contentID
LEFT JOIN link_countries ON content.contentID = link_countries.contentID
LEFT JOIN link_sections ON content.contentID = link_sections.contentID
WHERE status = '1' AND typeID = '1' AND (sectionID = '3' AND sectionID = '4')
GROUP BY contentID
ORDER BY creationDate DESC

Answer 1

您可以使用子选择:

SELECT *, content.contentID AS thisID
FROM content
LEFT JOIN link_keywords ON content.contentID = link_keywords.contentID
LEFT JOIN link_countries ON content.contentID = link_countries.contentID
LEFT JOIN link_sections ON content.contentID = link_sections.contentID
WHERE status = '1' AND typeID = '1' AND
      content.contentID IN (SELECT section3.contentID
                            FROM link_sections AS section3
                            WHERE sectionID = 3) AND
      content.contentID IN (SELECT section4.contentID
                            FROM link_sections AS section4
                            WHERE sectionID = 4)
GROUP BY contentID
ORDER BY creationDate DESC