开发者

MySQL - AND query on joined table

I have a pretty long SQL query:

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

I get back a set of results, what I would like to do is display all results where sectionID (which comes from the link_sections join) is equal to both 3 or 4. In other words, all results that are in both section 3 and 4.

The following query doesn't return back anything, presumably this is because it is checking for a single row with sectionID equal to 3 and 4. What I need is any content.contentID with sectionID equal to 3 and 4.

SELECT *, content.contentID AS thisID
FROM content
LEFT JOIN link_keywords ON content.contentI开发者_开发技巧D = 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


You could use sub-SELECTs:

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


Try this:

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 
(SELECT COUNT(contentID) AS counter FROM link_sections s2 WHERE s2.contentID=content.contentID AND (sectionID='3' OR sectionID='4') GROUP BY contentID)=2
    AND STATUS = '1'
    AND typeID = '1'
    AND (sectionID = '3'
         OR sectionID = '4')
GROUP BY content.contentID
ORDER BY creationDate DESC;


What you need to do is select the rows with sectionID = '3' and intersect the results with the rows with sectionID = '4'.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜