Bdtjtk

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

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

                    );

I think that you don’t need a correlated subquery. An aggregate JOIN query with a HAVING clause should do it, like :

SELECT

    S.STAFF_ID, 

    S.FIRST_NAME,

    S.LAST_NAME, 

    S.TITLE 

FROM 

    STAFF AS S

    INNER JOIN COURSE AS C

        ON S.STAFF_ID = C.STAFF_ID

GROUP BY

    S.STAFF_ID, 

    S.FIRST_NAME,

    S.LAST_NAME, 

    S.TITLE

HAVING 

    COUNT(*) >=2

;

This will retrieve all staff persons that have at least two courses.

You will need to link your sub query back to your main query. At the moment you are just looking for ANY staff member that has more than two courses in your sub query. Try the below (untested).

For clarity especially when you are using sub queries it is a good idea to alias all of your tables.

SELECT S.STAFF_ID, S.FIRST_NAME, S.LAST_NAME, S.TITLE 

FROM STAFF S, COURSE C

WHERE S.STAFF_ID = C.STAFF_ID

AND (SELECT C1.COURSE_ID FROM COURSE c1

     where s.staff_id = c1.staff_id

     HAVING COUNT(C1.COURSE_ID) >=2)

The above assumes that you need to get some columns out of course as well, however at the moment you have none in your first select so the query could be further simplified to the below if you do not need anything from course.

SELECT S.STAFF_ID, S.FIRST_NAME, S.LAST_NAME, S.TITLE 

FROM STAFF S

where (SELECT C.COURSE_ID FROM COURSE c

     where s.staff_id = c.staff_id

     HAVING COUNT(C.COURSE_ID) >=2)

If you want only staff details or else can join if needed course details as well

      SELECT Staff.* from Staff s where   

        s.staff_id in 

     (Select 

     staff_id from

           (SELECT  COURSE_ID,STAFF_ID

           FROM COURSE 

            GROUP BY STAFF_ID

       HAVING COUNT(COURSE.COURSE_ID) >=2)

        );

or via join infact even in join you require a subquery reason being group by when grouping by s.staff_id the c.course_id values will become inconsistent and unsync with s.staff_id

           Select s.* ,c.COURSE_ID from staff s

            join course c

            on s.staff_id IN (Select c.staff_id group by 

               c.staff_id

             HAVING COUNT(*) >=2)