CODEWITHSUNDEEP
sqlsql-server
matrixfox
matrixfox

Reputation: 83

SQL Select Help - Subqueries

I'm trying to select the students who do not have a score for assignment 11, and return their names. Here are my tables & attempted solution.. each select works separately but not together.

CREATE TABLE Student(
    Student_ID INT,
    Student_Name VARCHAR(10)
    )
INSERT INTO Student (Student_ID, Student_Name) VALUES (1,'Alex'),(2, 'Brett1'),(3,'Cora'),(4,'David'),(5,'Eleanor'),(6,'Brett2')

CREATE TABLE Grades(
    Student_ID INT,
    Assignment_ID INT,
    Grade INT
    )
INSERT INTO Grades (Student_ID, Assignment_ID, Grade) VALUES (1,10,90),(2,10,85),(3,10,75),(4,10,74),(1,11,80),(2,11,81),(4,11,88),(6,11,86),(2,12,84)`

SELECT *
    FROM Student s
LEFT OUTER JOIN Grades g on s.Student_ID=g.Student_ID
    WHERE g.Student_ID NOT IN(
    SELECT * FROM Grades g
    WHERE g.Assignment_ID = 11
    )

Upvotes: 1

Views: 92

Answers (5)

that-ben
that-ben

Reputation: 265

SELECT Student_Name FROM Student AS s
LEFT JOIN Grades AS g
ON s.Student_ID = g.Student_ID AND g.Assignment_ID = 11
WHERE g.Grade IS NULL

enter image description here

Note that if these were very large tables there would be a small performance hit with the above compared to @SeanLange answer which might execute a tiny bit faster if all indices are properly set on each very large tables:

SELECT Student_Name FROM Student s
WHERE NOT EXISTS
(
    SELECT * FROM Grades g
    WHERE g.Student_ID = s.Student_ID AND g.Assignment_ID = 11
)

Upvotes: 0

kjmerf
kjmerf

Reputation: 4335

I like Sean Lange's answer, but you could also use a sub-query and LEFT JOIN:

SELECT s.student_name
FROM student s
LEFT JOIN
(SELECT student_id
 FROM grades
 WHERE assignment_id = 11) sub
ON s.student_id = sub.student_id
WHERE sub.student_id IS NULL

Upvotes: 0

Eray Balkanli
Eray Balkanli

Reputation: 7960

You can use a query like:

;with cte (stId,asgId) as
(
   select s.Student_ID as stId ,g.Assignment_Id as asgId
   from Student s
   inner join Grades g on s.Student_Id = g.Student_ID
   where g.Assignment_Id = 11
)
select distinct Student_Name
from Student s
where s.Student_ID not in (select stId from cte)

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

I would use NOT EXISTS with a correlated subquery here.

select *
from Student s
where not exists
(
    select *
    from Grades g
    where g.Student_ID = s.Student_ID
        AND g.Assignment_ID = 11
)

Upvotes: 1

SQL_M
SQL_M

Reputation: 2475

You were alomst there:

SELECT Student_ID 
FROM Student s
WHERE g.Student_ID NOT IN(
                           SELECT Student_ID FROM Grades g
                           WHERE g.Assignment_ID = 11 
                             AND Student_ID IS NOT NULL)

Upvotes: 0

Related Questions