MySQL Query Question

Question

Say I have the following 3 tables:

users
id : int 11 primary autoinc
email : varchar 255

tags
id : int 11 primary autoinc
name : varchar 255

tag_union
id : int 11 primary autoinc
tag_id : int 11
target_id : int 11
target_type : enum( 'user','blog','other' )

If I'm given a list of tags, say 1,3, and 8, I can select all of the users that have a union of ANY of the tags like so:

SELECT *
FROM `users`
WHERE `id` IN( SELECT `target_id`
               FROM `tag_union`
               WHERE `tag_id` IN( '1','3','8')
                 && `target_type`=='user' )

But how would I select only users that have a union for ALL 3 of the tags? I can't seem to think of a way to do this in a single query.

P.S. Sorry for the crappy title, someone can rename it if they can think of a more fitting one.

How many rows do you have in each table? Is performance an issue here? — Mark Byers
– Mark Byers, Commented Jan 13, 2011 at 21:17
@Mark: Currently the users table is max 50K rows, and the tag table ~2k, with the union being somewhere in the middle. Most people have only like one or tag. — Josh
– Josh, Commented Jan 13, 2011 at 21:42
I suspect that with 50K rows in the user tables the IN will take several seconds or more, whereas the JOIN will be almost instant. But if I'm wrong about that, I'd like to know so that I can learn. — Mark Byers
– Mark Byers, Commented Jan 13, 2011 at 21:45

Mark Byers · Accepted Answer · 2011-01-13 21:41:23Z

2

Your derived table should use a GROUP BY / HAVING:

SELECT *
FROM users
WHERE id IN
(
    SELECT target_id
    FROM tag_union
    WHERE tag_id IN ('1', '3', '8') AND target_type = 'user'
    GROUP BY target_id
    HAVING COUNT(*) = 3
)

Note that in MySQL the IN clause can be very slow. It might be better to use a join instead.

SELECT *
FROM users T1
JOIN
(
    SELECT target_id
    FROM tag_union
    WHERE tag_id IN ('1', '3', '8') AND target_type = 'user'
    GROUP BY target_id
    HAVING COUNT(*) = 3
) T2
ON T1.id = T2.target_id

I'm also assuming that (target_type, tag_id) is unique in the tag_union table.

edited Jan 13, 2011 at 21:41

answered Jan 13, 2011 at 21:08

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

4 Comments

Josh Over a year ago

It isn't distinct, a tag can be on multiple types, and a type can have multiple tags

Mark Byers Over a year ago

@Josh: From your reply, I think you may have misunderstood what it means for a tuple to be distinct. I know the same tag can appear on multiple different types, but can the same tag appear on a single type (e.g. user) multiple times? And if it can, why is that necessary in your design?

Josh Over a year ago

No, it can't, sorry I misunderstood.

Mark Byers Over a year ago

@Josh: Sorry that I missed the GROUP BY in my answer before. I fixed that now.

Joe Stefanelli · Accepted Answer · 2011-01-13 21:09:18Z

0

SELECT *
FROM `users`
WHERE `id` IN( SELECT `target_id`
               FROM `tag_union`
               WHERE `tag_id` IN ('1','3','8')
                 AND `target_type`='user' 
               GROUP BY `target_id`
               HAVING COUNT(DISTINCT `tag_id`) = 3)

answered Jan 13, 2011 at 21:09

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

Ronnis · Accepted Answer · 2011-01-13 22:05:04Z

0

I usually do it with the group by/having approach the other suggested. But just for completeness, it can also be done with joins.

select u.id
      ,u.other_columns
  from users u
  join tag_union t1 on(u.id = t1.target_id)
  join tag_union t2 on(u.id = t2.target_id)
  join tag_union t3 on(u.id = t3.target_id)
 where t1.target_type = 'user' and t1.tag_id = 1
   and t2.target_type = 'user' and t2.tag_id = 3
   and t3.target_type = 'user' and t3.tag_id = 8;

answered Jan 13, 2011 at 22:05

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

Collectives™ on Stack Overflow

MySQL Query Question

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related