Search code examples
sqloracle-databasecorrelated-subquery

Correlated Query with more than one table SQL

I need to list some staff details who are registered for 2 or more courses using a sub query. The staff_id in the staff table is linked to the course table, so s.staff_id = c.staff_id. But I'm really confused and don't know how to correct.

SELECT STAFF.STAFF_ID, STAFF.FIRST_NAME, STAFF.LAST_NAME, STAFF.TITLE, COURSE.ID 
FROM STAFF, COURSE C
WHERE STAFF.STAFF_ID = COURSE.STAFF_ID
AND (SELECT COURSE.COURSE_ID FROM COURSE 
GROUP BY STAFF.STAFF_ID
HAVING COUNT(COURSE.COURSE_ID) >=2);
Solution

  • If you want staff that have more than one course, a join is not necessary -- implicit or explicit. Here is a more direct approach:

    select s.*
from staff s
where s.staff_id in (select c.staff_id
                     from course c
                     group by c.staff_id
                     having count(*) >= 2
                    );