CODEWITHSUNDEEP
sqlmany-to-many
Sawyer
Sawyer

Reputation: 45

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?

Upvotes: 1

Views: 229

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 1

x13
x13

Reputation: 2227

Using a subquery that we shall name "Temp" that contains a list of students that did attend a lecture with more than 4 ECTS, we can select from Students that are not in that list with a LEFT JOIN:

SELECT
  ST.[S#],
  ST.SNAME
FROM ST
LEFT JOIN
  (SELECT LS.[S#]
    FROM SL
    JOIN L
    ON SL.[L#] = L.[L#]
    WHERE L.ECTS > 4
  ) AS Temp
ON ST.[S#] = Temp.[S#]
WHERE Temp.[S#] IS NULL

Upvotes: 0

Popeye
Popeye

Reputation: 35920

You can use not exists as follows:

SELECT S.[S#], SNAME
  FROM ST
  INNER JOIN LS ON LS.[S#] = S.[S#]
WHERE not exists
      (select 1 from L
        Where L.[L#] = LS.[L#]
          AND L.ECTS > 4)

Upvotes: 1

Related Questions