Or Duer Or Duer - 2 years ago 60
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download