CODEWITHSUNDEEP
mysqlgroup-by
Richard H
Richard H

Reputation: 39135

Mysql: SELECT and GROUP BY

Sorry for the abysmal title - if someone wants to change it for something more self-explanatory, great - I'm not sure how to express the problem. Which is:

I have a table like so:

POST_ID (INT)   TAG_NAME (VARCHAR)

    1              'tag1'
    1              'tag2'
    1              'tag3'
    2              'tag2'
    2              'tag4'
   ....

What I want to do is count the number of POSTs which have both tag1 AND tag2.
I've messed about with GROUP BY and DISTINCT and COUNT but I can't construct a query which does the trick.
Any suggestions?

Edit: In pseudo sql, the query I want is:

SELECT DISTINCT(POST_ID) WHICH HAS TAG_NAME = 'tag1' AND TAG_NAME = 'tag2';

Thanks

Upvotes: 0

Views: 187

Answers (3)

Konerak
Konerak

Reputation: 39773

Edit: because 'TABLE' was a poor choice for a missing tablename, I'll suppose your table is called Posts.

Join the table against itself:

SELECT * FROM Posts P1
JOIN Posts P2
ON P1.POST_ID = P2.POST_ID
WHERE P1.TAG_NAME = 'tag1'
AND P2.TAG_NAME = 'tag2'

Upvotes: 2

Bruno Gautier
Bruno Gautier

Reputation: 656

Try the following query:

SELECT COUNT(*) nb_posts
FROM (
    SELECT post_id, COUNT(*) nb_tags
    FROM table
    WHERE tag_name in ('tag1','tag2')
    GROUP BY post_id
    HAVING COUNT(*) = 2
  ) t

Edit: based on Konerak answer, here is the query that handles the case when there are duplicated tag names for a given post:

SELECT DISTINCT t1.post_id
FROM table t1
  JOIN table t2
    ON t1.post_id = t2.post_id
       AND t2.tag_name = 'tag2'
WHERE t1.tag_name = 'tag1'

Upvotes: 0

Matchu
Matchu

Reputation: 85862

I'm just leaving this (untested) dependent subquery solution here for reference, even though it'll probably be horribly slow once you get to large data sets. Any solution that does the same thing using joins should be chosen over this.

Assuming you have a posts table with an id field, as well:

SELECT count(*) FROM posts WHERE EXISTS(SELECT NULL FROM posts_tags WHERE tag = 'tag1' AND post_id = posts.id) AND EXISTS(SELECT NULL FROM posts_tags WHERE tag = 'tag2' AND post_id = posts.id)

Upvotes: 0

Related Questions