This query fetches news articles that are tagged similarly to other news articles. Is it possible I can eliminate one of these subqueries that I'm not seeing?
SELECT id,
title,
comment_count
FROM NEWS
WHERE (franchise_id = 1 OR franchise_id = 0)
AND draft = 0
AND id != 459
AND id IN (SELECT news_id
开发者_如何学Go FROM news_tag_association
WHERE tag_id IN (SELECT tag_id
FROM news_tag_association
WHERE news_id = 459))
LIMIT 0, 5;
Yes, there is no GROUP BY or similar, so you can use normal self-joins:
SELECT
id, title, comment_count
FROM news
JOIN news_tag_association n2t1 ON (news.id = n2t1.news_id)
JOIN news_tag_association n2t2 ON (n2t1.tag_id = n2t2.tag_id)
WHERE franchise_id IN (0, 1)
AND draft = 0
AND id != 459
AND n2t2.news_id = 459
LIMIT 0,5;
精彩评论