SELECTing n rows for each GROUP with WHERE condition

Question

I SELECT n rows for each ArticleID from TagMap table

SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
HAVING COUNT(b.TagID) < 3
ORDER BY a.ArticleID,a.TagID

I want to add a condition to limit the query to specific tags,

SELECT a.ArticleID,a.TagID FROM TagMap a
  LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
    WHERE a.TagID IN(SELECT TagID FROM Tags WHERE Status=1)
    AND b.TagID IN(SELECT TagID FROM Tags WHERE Status=1)
  GROUP BY a.ArticleID,a.TagID
  HAVING COUNT(b.TagID) < 3
  ORDER BY a.ArticleID,a.TagID

If I use the condition as sub-query, I would need to sub-queries. If I use the condition as JOIN, I need two JOINs since the above JOIN is LEFT JOIN.

Is there an easier way to do so?

Answer 1

You could add the status conditions to th on clause

  SELECT a.ArticleID,a.TagID FROM TagMap a
      LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID AND a.Status =1 AND b.Status = 1
      GROUP BY a.ArticleID,a.TagID
      HAVING COUNT(b.TagID) < 3
      ORDER BY a.ArticleID,a.TagID

the subselects aren't needed at al if a status is a independent if a or b you take the row, if it isn't you dismiss it.

If you put the status condition in a WHERE clause or in the ON doesn't change the execution plan.

But you could change the index to include the Status