SQL Finding all elements which does not belong to a particular set, in many to many relation

I have problem with this task:

Given are the relations

Students: ST (S#, SNAME)

Lecture: L (L#, LNAME, ECTS)

Who attends what: LS (L#, S#)

Find students (S# and SNAME) who do not attend any lecture having more than 4 ECTS

I know that I can do it by checking whether the MAX value of ECTS for given student is greater than 4, but as part of learning I want to do it differently. Namely I want to do it using subqueries:

SELECT S.[S#], SNAME
FROM ST
INNER JOIN LS ON LS.[S#] = S.[S#]
WHERE LS.[L#] /* and here is a problem, I want a statement like "none of LS.[L#] exists in" */ (SELECT [L#] FROM L WHERE ECTS > 4)

My idea is that (SELECT [L#] FROM L WHERE ECTS > 4) will return all of the Lectures for which ECTS is greater than 4, and then I just need to check whether there exist in this set one of those which are assigned to Student via LS table, and then skip it.

I know that there exist operator like ALL and ANY, but it doesn’t seem to work in any configuration.

I tried for example WHERE NOT LS.[L#] = ANY (SELECT [L#] FROM L WHERE ECTS > 4) but since it operates separetly per LS.[L#], it just returned me students which have at least one Lecture with ECTS <= 4.

I figured out that WHERE LS.[L#] = ANY (SELECT [L#] FROM L WHERE ECTS > 4) gives me exactly negation of a set that I want – naivly I thought that NOT would invert this set – but aparently no – what I want is in this case would be ALL STUDENTS - WHERE LS.[L#] = ANY (SELECT [L#] FROM L WHERE ECTS > 4).

Is there a neat solution to this problem, in this kind of manner?

Answer

You can use NOT EXISTS, but the correct logic is:

SELECT S.[S#], s.SNAME
FROM S
WHERE NOT EXISTS (SELECT 1
                  FROM LS JOIN
                       L
                       ON L.[L#] = LS.[L#]
                  WHERE LS.[S#] = S.[S#] AND L.ECTS > 4
                 ) ;

That is, you want to join in the subquery.