MySQL Many-To-Many Query Problem

This query does the job:

select  user_idfrom    user_has_personalitieswhere   personality_id in (<list-of-personality-ids>)group by user_idhaving 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_idfrom    user_has_personalitieswhere   personality_id in (4,5,7)group by user_idhaving count(*) = 3

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

SELECT  *FROM    (        SELECT  DISTINCT user_id        FROM    user_has_personalities        ) uhpoWHERE   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        ) uhpoWHERE   (        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_idFROM    users uhpoWHERE   (        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        )

SELECT a.user_idFROM user_has_personalities aJOIN user_has_personalities b ON a.user_id = b.user_id AND b.personality_id = 5JOIN user_has_personalities c ON a.user_id = c.user_id AND b.personality_id = 7WHERE a.personality_id = 4

It would be easy enough to generate this list programatically, but it's not exactly as easy as supplying a set. On the other hand, it is efficient.