Search code examples
phpmysqlsqlrelational-division

mysql select multitable - join

say i had the following tables 

user_table 
id    username
1     abc
2     def
3     ghij

courses_table 
id    title
1     csc
2     math
3     syn

user_courses 
user_id    course_id
2           1
1           3
2           3

i want to select the username whos taking course 1 AND 3 , not at least 1 or 3 , i mean both 1 and 3

i've tried the following mysql queries 

SELECT DISTINCT  u.* FROM  user_table as u  LEFT JOIN user_courses as uc ON uc.user_id = u.id  WHERE uc.course_id = 1 AND uc.course_id=3;
SELECT DISTINCT  u.* FROM  user_table as u  LEFT JOIN user_courses as uc ON uc.user_id = u.id  WHERE uc.course_id IN (1,3);
SELECT DISTINCT  u.* FROM  user_table as u  LEFT JOIN user_courses as uc ON uc.user_id = u.id  WHERE uc.course_id IN (1,3) AND uc.user_id = u.id ;

the first and third queries executed with no results shown , and the second one show all users who had at least course_id 1 or 3

if you are wondering why am i using the LEFT JOIN , this is because i need to join table's results , and the above line of code is just an example , and im using to get data from about 9 tables using the LEFT join .

any help please ? thanks 

SELECT DISTINCT  u.* FROM  user_table as u  LEFT JOIN user_courses as uc ON uc.user_id = u.id  WHERE uc.course_id IN( 1,3) AND uc.user_id = 2 ";

this show me the result i want , its output "def" , but i can't use the user_id as a static value ( number 2 in this example )

Solution

  • This problem is called Relational Division

    SELECT  a.id, a.username
FROM    user_table a
        INNER JOIN user_courses b
            ON a.id = b.user_ID
WHERE   b.course_ID IN (1,3)
GROUP   BY a.id, a.username
HAVING  COUNT(*) = 2

    If course_ID is not unique for every users considering that the user have retake the course, a DISTINCT keyword is needed to coung unique courses,

    SELECT  a.id, a.username
FROM    user_table a
        INNER JOIN user_courses b
            ON a.id = b.user_ID
WHERE   b.course_ID IN (1,3)
GROUP   BY a.id, a.username
HAVING  COUNT(DISTINCT b.course_ID) = 2

    OUTPUT

    ╔════╦══════════╗
║ ID ║ USERNAME ║
╠════╬══════════╣
║  2 ║ def      ║
╚════╩══════════╝