MySQL“FIELD_IN_SET（）”等效于“已加入”表

Question

I'm searching for an equivalent for MySQL "FIELD_IN_SET()" with joined tables. Here is an example of what I want to do.

I have three tables. The first contains fields "user_id" and "user_name". The second contains fields "id", "user_id" and "role_id". The third contains "id", "role_name".

I want to search for users that owns two different specified roles. After LEFT JOINs, my table will look like this :

user_id - user_name          - role_name
12      - Yoda               - jedi
12      - Yoda               - master
15      - Obi-Wan Kenobi     - jedi

The first question is : How to search for users that are both "jedi" and "master". The second question is : Is it better (optimization || readability) to do it on PHP side (getting all results in an array and do some loop to verify what I want to do) or directly in the MySQL query, knowing that I will have to build the query dynamically (it's for a PHP/MySQL search engine in a great user database).

Thank you :) . user1527491

Answer 1

This problem is called RELATION DIVISION

SELECT  a.user_name
FROM    firstTable a
        INNER JOIN secondTable b
            ON a.user_ID = b.user_ID
        INNER JOIN thirstTable c
            ON b.role_ID = c.role_ID
WHERE   c.role_NAME IN ('jedi', 'master')
GROUP   BY a.user_name
HAVING  COUNT(*) = 2

• SQL of Relational Division

If a unique constraint was not define for role_NAME for every user_name, DISTINCT keyword is needed.

SELECT  a.user_name
FROM    firstTable a
        INNER JOIN secondTable b
            ON a.user_ID = b.user_ID
        INNER JOIN thirstTable c
            ON b.role_ID = c.role_ID
WHERE   c.role_NAME IN ('jedi', 'master')
GROUP   BY a.user_name
HAVING  COUNT(DISTINCT c.role_NAME) = 2