Question 230694 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query for EventId's that match multiple TagId's

I have a many to many table that holds both EventId and TagId.

+-------+---------+
| TagId | EventId |
+-------+---------+
|   150 |   61096 |
|   149 |   61096 |
|   149 |   61095 |
+-------+---------+

How can I query for only EventId’s that match both TagId 149 and TagId 150?

Desired result:

+---------+
| EventId |
+---------+
|   61096 |
+---------+
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This works for finding the events that have only these two tags:

    SELECT EventId
FROM Events_Tags
WHERE TagId IN (149, 150)
GROUP BY EventId
HAVING COUNT(*) = 2;

    This works for finding the events that have at least these two tags:

    SELECT EventId
FROM Events_Tags
WHERE TagId IN (149, 150)
GROUP BY EventId
HAVING COUNT(*) >= 2;

  2. 0

    This can be done using :

    SELECT EventId
FROM Events_Tags
WHERE TagId IN (149, 150)
GROUP BY EventId
HAVING COUNT(CASE WHEN TagId = 149 then 1 end) = 1  AND COUNT(CASE WHEN TagId = 149 then 1 end) = 1;

    Or simply :

    SELECT EventId
FROM Events_Tags
WHERE TagId IN (149, 150)
GROUP BY EventId
HAVING COUNT(DISTINCT TagId) = 2;

    Demo here

    Login or Signup to reply.
  3. 0 
    SELECT DISTINCT EventId
FROM table t1
JOIN table t2 USING (EventId)
WHERE t1.TagId = 149
  AND t2.TagId = 150

    If (EventId, TagId) is unique then DISTINCT not needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search