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
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
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