Mysql: how to select groups having certain values?

Question

Say there is such table:

mysql> SELECT * FROM tags;
+---------+--------+
| post_id | tag_id |
+---------+--------+
|       1 |      2 |
|       1 |      3 |
|       1 |      1 |
|       2 |      1 |
|       2 |      2 |
+---------+--------+
5 rows in set (0.00 sec)

Field names are pretty self-explanatory. I want to select post_ids that have both 1 and 3 tag_ids, so in this example it's only 1. I thought of something like SELECT post_id FROM tags GROUP BY post_id HAVING ... After having I'd like to list tag_ids that are present in this group. How do I do that?

Answer 1

If there aren't any unique constraints try:

SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(DISTINCT tag_id) = 2;

Or use this HAVING clause, if trying to detect only two tag_id values:

HAVING MIN(tag_id) <> MAX(tag_id)

If post_id and tag_id both have an unique constraint, this should work too:

SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(*) = 2;

Answer 2

You could try a self join (N tag_id -> N join) but probably it's not fast

SELECT t1.post_id 
FROM tags t1 INNER JOIN tags t2 ON t1.post_id = t2.post_id 
WHERE t1.tag_id = 1 AND t2.tag_id = 3

Answer 3

SELECT post_id
  FROM ( SELECT post_id,
                count(tag_id) AS counter
           FROM tags
          WHERE tag_id IN (1,3)
          GROUP BY post_id
       )
 WHERE counter = 2

Use GROUP_CONCAT() for the second part of your question

SELECT post_id,
       GROUP_CONCAT(tag_id ORDER BY tag_id ASC SEPARATOR ',')
  FROM tags

Answer 4

I've made some assumptions about your other tables. (i.e. that you have a table for posts that I have called posts and one with tag_id as the PK which I have called tag_table to avoid a nameclash with the posts/tags table that I can see you already call tags)

You want posts where there does not exist a tag in the list {1,3} for which there does not exist a matching record with the corresponding post_id/tag_id so you can use a double NOT EXISTS construct as below.

SELECT post_id
FROM posts p
WHERE NOT EXISTS 
    (SELECT * FROM tag_table tt
    WHERE tag_id IN (1,3)
    AND NOT EXISTS
        (SELECT * FROM tags t
        WHERE t.tag_id = tt.tag_id  and
        p.post_id = t.post_id)        
    )

Another alternative approach is to use Group By and Count. A review of approaches to this problem is here.

Answer 5

How about

SELECT * 
FROM tags 
WHERE post_id in 
  (SELECT post_id AS pid 
   FROM tags 
   WHERE 1 IN (SELECT tag_id FROM tags WHERE post_id = pid) 
   AND 3 IN (SELECT tag_id FROM tags WHERE post_id = pid)
  );

Answer 6

WHERE version of @Keeper's solution

SELECT DISTINCT t1.post_id 
FROM tags t1, tags t2
WHERE 
  t1.post_id = t2.post_id  AND 
  t1.tag_id = 1 AND t2.tag_id = 3