MySQL Many-To-Many Query Problem

Question

Here's my problem. I have a many-to-many table called 'user_has_personalities'. In my application, users can have many personalities, and a personality can belong to many users.

The table has two integer columns, user_id and personality_id.

What I need to do is get all users that have at least all of the personalities (a set of personality_ids of variable size) which I supply to the query.

For an example, I'd like to get all users that have personalities with ids 4, 5, 7, but can also have some other personalities. But I need the query to work for a variable number of wanted personality ids, like 4, 5, 7, 9, 10 for an example.

Any ideas?

Answer 1

This query does the job:

select  user_id
from    user_has_personalities
where   personality_id in (<list-of-personality-ids>)
group by user_id
having count(*) = <numer-of-items-in-IN-list>

You need to supply a comma-separated list of personality ids for <list-of-personality-ids> and you also need to provide the number of items in th elist. Sticking to your example, you would get:

select  user_id
from    user_has_personalities
where   personality_id in (4,5,7)
group by user_id
having count(*) = 3

this ensures you only get users that have all these personalities.

Answer 2

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    user_has_personalities
        ) uhpo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    user_has_personalities uhpi
        WHERE   uhpi.user_id  = uhpo.user_id
                AND personality_id IN (4, 5, 6, 9, 10)
        LIMIT 1 OFFSET 4
        )

Offset value should be 1 less than the number of items in the IN list.

If you have your personality list in a dedicated table, use this:

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    user_has_personalities
        ) uhpo
WHERE   (
        SELECT  COUNT(*)
        FROM    perslist p
        JOIN    user_has_personalities uhpi
        ON      uhpi.user_id = uhpo.user_id
                AND uhpi.personality_id = p.id
        ) =
        (
        SELECT  COUNT(*)
        FROM    perslist
        )

For this to work correctly (and fast), you need to have a UNIQUE index on user_has_personalities (user_id, personality_id) (in this order).

If you have a users table and almost all users have a record in user_has_personalities, then substitute it in place of the DISTINCT nested query:

SELECT  user_id
FROM    users uhpo
WHERE   (
        SELECT  COUNT(*)
        FROM    perslist p
        JOIN    user_has_personalities uhpi
        ON      uhpi.user_id = uhpo.user_id
                AND uhpi.personality_id = p.id
        ) =
        (
        SELECT  COUNT(*)
        FROM    perslist
        )