Mysql: SELECT and GROUP BY

Question

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

It'd be helpful to also see what rows you want the query to return, since I'm still not totally sure what you're asking. — Matchu
– Matchu, Commented Nov 25, 2010 at 13:04
@Matchu - in pseudo sql: select post_id which has tag_name = 'tag1' and tag_name = 'tag2'; — Richard H
– Richard H, Commented Nov 25, 2010 at 13:06

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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'

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 25, 2010 at 13:04

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richard H Over a year ago

thanks this looks promising. My actual problem is a little more complicated as my table is not really a table, it's the result of a select+join on two normalised tables.

Bruno Gautier Over a year ago

@Richard, simply replace in the query the TABLE by (<your query here>).

Richard H Over a year ago

I think your query should actually be: SELECT * FROM T1 JOIN T1 as T2 ON T1.POST_ID = T2.POST_ID WHERE T1.TAG_NAME = 'tag1' AND T2.TAG_NAME = 'tag2'

Konerak Over a year ago

@Richard: that is exactly the same query? You didn't supply a tablename, so I used "TABLE" is table name - don't confuse it with the keyword "TABLE". I edited the answer for clarity.

Matchu · Accepted Answer · 2010-11-25 13:07:54Z

0

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)

answered Nov 25, 2010 at 13:07

Matchu

86.1k18 gold badges155 silver badges160 bronze badges

Comments

Bruno Gautier · Accepted Answer · 2010-11-25 13:24:29Z

0

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'

edited Nov 25, 2010 at 13:24

answered Nov 25, 2010 at 13:04

Bruno Gautier

6564 silver badges7 bronze badges

4 Comments

Konerak Over a year ago

What happens when an entry is in the table two times? 1-tag1, 1-tag1?

Bruno Gautier Over a year ago

@Konerak, when it is the case, do you want to include it into the count, or exclude it ?

Matchu Over a year ago

@Konerak: good point. I'm betting it shouldn't happen, but it'd probably be best to avoid that possibility if doable. @Bruno: I think the point is that counting is not the best solution. Maybe it'd work if you used a DISTINCT somewhere in there, but I'm not sure.

Bruno Gautier Over a year ago

@Matchu, if you want to select the posts and not count them, I think that the query suggested by Konerak is the best solution. But the query as it is does not work if you have duplicated tag names for a given post_id.

Collectives™ on Stack Overflow

Mysql: SELECT and GROUP BY

3 Answers 3

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

4 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

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

4 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related

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