SQL- Find all students who all of their friends have score less than 10-CodePudding

I have to write some simple queries for my database homework using SQLServer. The schema has been provided in the question:

students(student_id, name);
friends(student_id, friend_id);
db_grades(student_id, grade);

For every pair of friends, 2 rows like (x, y) and (y, x) would be added to the friends table

Find all students, who all of their friends have a score of less than 10

First I tried to solve this question using GROUP BY, and I wrote the below query.

Select Distinct s.name, MAX(g.grade)
       From (students s inner join friends f on s.student_id = f.student_id), db_grades g
       Where f.friend_id = g.student_id
       Group by f.friend_id, s.name
       Having MAX(g.grade) < 10;

And in the second try, I wanted to implement it using the IN keyword, but it also does not work correctly.

Select distinct s.name
    From students s  join friends f on s.student_id = f.student_id
    Where f.friend_id not in
    (
       Select f.friend_id
       From  db_grades g1
       Where g1.student_id = f.friend_id and g1.grade > 10
    )

I will be grateful for your help on how to write the correct query.

CodePudding user response：

Aggregation is one option:

SELECT s.student_id, s.name
FROM students s
INNER JOIN friends f
    ON f.student_id = s.student_id
INNER JOIN db_grades g
    ON g.student_id = f.friend_id
GROUP BY s.student_id, s.name
HAVING COUNT(CASE WHEN f.grade >= 10 THEN 1 END) = 0;

CodePudding user response：

Another option is to use NOT EXISTS. Note that the difference between this and HAVING with an INNER JOIN is that this option includes those that have no friends at all.

SELECT s.student_id, s.name
FROM students s
WHERE NOT EXISTS (SELECT 1
    FROM friends f
    INNER JOIN db_grades g
        ON g.student_id = f.friend_id
    WHERE f.student_id = s.student_id
      AND g.grade >= 10
);