Sql list unregistered classroom-CodePudding

There is one student. This student is enrolling in classes. What I want is to list the classes where this student is not registered. My SQL query is like this, where am I going wrong?

SELECT * FROM class, userClass WHERE class.id <> userClass.class AND userClass.user<>$userid

CodePudding user response：

I had to assume your table structure and I hope I got it right. The classic approach would be with LEFT JOIN and IS NULL:

select
    c.*
from
    class c left join userClass uC on c.id = uC.class and uC.user = $userid
where
    uC.class is null

or using NOT IN

select
    *
from
    class
where
    id not in (select class from userClass where user = $userid)

BTW: You should read a bit about database naming conventions - like using plural names for tables (classes, userClasses) and properly identifying keys - if table classes has an id then you should refer to it in related tables by classId, etc.