CODEWITHSUNDEEP
mysqldatabase
Josh
Josh

Reputation: 12566

MySQL Query 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.

Upvotes: 0

Views: 63

Answers (3)

Ronnis
Ronnis

Reputation: 12843

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;

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839124

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.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

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)

Upvotes: 0

Related Questions