CODEWITHSUNDEEP
mysqlsql
koshi18
koshi18

Reputation: 53

mysql query to get all the unit that a student not enrolled in

I have two tables: chosenunit and units. In chosenunit table there are 2 columns student_id and unit_id . Units table has unit_id and unit_name columns with all the units in the system. Lets say there are 2 students with ids of 1000 and 1001. They are enrolled in several units. chosenunit table will look like this:

(1000,2000),
(1000,2001),
(1000,2006),
(1001,2000),
(1001,2004),

I need to get all the units that a student is not enrolled in. If 1000 logs in to the system,it need to show all the unit_names of all the subjects except 2000,2001,2006.

Upvotes: 1

Views: 609

Answers (1)

John Woo
John Woo

Reputation: 263723

There are many possible solutions on this problem.

first is by using LEFT JOIN and IS NULL.

SELECT  a.*
FROM    units a
        LEFT JOIN choseunits b
            ON a.unit_ID = b.unit_ID AND
                b.student_ID = 1000
WHERE   b.unit_ID IS NULL

To further gain more knowledge about joins, kindly visit the link below:

Second is by using NOT EXISTS

SELECT  a.*
FROM    unit a
WHERE   NOT EXISTS
        (
            SELECT  1
            FROM    choseunit b
            WHERE   a.unit_ID = b.unit_ID AND
                    b.student_ID = 1000
        )

Upvotes: 6

Related Questions