Or Duer Or Duer - 7 months ago 16
SQL Question

Select and group results using the same column as a parameter

I have a query that returns the following result (example):

+----+-----------+------------+
| ID | FirstName | CourseName |
+----+-----------+------------+
| 1 | Alice | X |
| 2 | Bob | X |
| 2 | Bob | Y |
+----+-----------+------------+


the query takes 3 tables (users, user-courses and course), and using JOIN returns the id of the user and his first name, and all the names of all courses he is in.

i need to create a query which returns users who are in specific courses, for example:


  • select all the users in course X: will return the details both of Alice and Bob.

  • select all users in courses X AND Y: will return only Bob, since alice isn't in course Y.



the result of the query X AND Y will be:

+----+-----------+
| ID | FirstName |
+----+-----------+
| 2 | Bob |
+----+-----------+

Answer

Assuming that user table and course table have an id and a name columns, and user-courses has only foreign key ids, you can do the following:

For the first question:

select u.* from user u
inner join user-courses uc on uc.user_id=u.id
inner join course c on c.id=uc.course_id and c.name='X';

It filters the user on inner joins, and filter the course on tha last part (c.name = 'X'). You can filter in any other way.

For the second one:

select * from user
where id in (
    select distinct a.* from (
        select user_id from user-courses uc inner join course c
        on c.id=uc.course_id
        where c.name='X'
    ) a
    inner join (
        select user_id from user-courses uc inner join course c
        on c.id=uc.course_id
        where c.name='Y' 
    ) b
    on a.user_id=b.user_id
);

MS-Access don't have intersect, so I used inner join (between a and b) to achieve the same results. A is the table with users from course 'X' and b from 'Y'. The inner join intersect both, resulting in users that are in both courses. Then I used the ids to filter.

I don't have MS-access, so I tried in PostgreSQL, but I used SQL-ANSI, so I hope so.

Comments