0

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?

4
  • The wanted result is not very clear. You want to show all articles but only 3 tags per article? Sep 10 '21 at 17:36
  • @ypercubeᵀᴹ I edited the query to clarify. I want 3 tags per article.
    – Googlebot
    Sep 10 '21 at 17:43
  • Can you please give an example of the before data and the after data output you're looking for? It's still a bit unclear what you're trying to achieve. Your WHERE clause has the same condition written twice (WHERE a.TagID IN(SELECT TagID FROM Tags WHERE Status=1)) which doesn't do anything differently logically.
    – J.D.
    Sep 11 '21 at 0:59
  • @J.D. sorry, it was a typo. The second a.TagID is b.TagID.
    – Googlebot
    Sep 11 '21 at 1:12
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

2
  • so silly of me, not to think this way!
    – Googlebot
    Sep 11 '21 at 17:23
  • it took me also some time, to figure it out
    – nbk
    Sep 11 '21 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.