Selecting ALL records when condition is met for ALL records only

Question

Sorry if this has been asked before. I couldn't find any examples.

I am trying to pull a student's course work for a semester, only if they have received a grade of 'NA' in ALL their courses. Right now, my code is only pulling any student who has a grade of 'NA' in any course. I need for them to have 'NA' in ALL courses, not just 1 or 2 courses.

My data:

Name	Course	Grade
student1	en101	NA
student1	ma101	B
student1	py102	A
student2	en101	NA
student2	ma205	NA
student2	en206	NA
student3	ma101	NA

I am trying to pull ALL rows for a student, ONLY if they have a grade = 'NA' in all their courses.

Results should be:

Name	Course	Grade
student2	en101	NA
student2	ma205	NA
student2	en206	NA
student3	ma101	NA

my code is pulling every row that has a grade of 'NA', even if the other rows don't meet the condition. I need to pull ALL rows for that record, only if it meets the condition for every row.

Seems easy in my mind...can't seem to make it work. thanx

Justin Cave · Answer 1 · 2021-01-07T19:17:31.327

Something like this would probably be the most conventional approach:

SELECT
  Name,
  Course,
  Grade
FROM
  myTable a
WHERE
  NOT EXISTS 
    (
      SELECT
        1
      FROM
        myTable b
      WHERE
        b.name = a.name
          AND b.grade <> 'NA' 
    )

Somewhat less conventional but potentially more efficient:

SELECT
 Name,
 Course,
 Grade
FROM
  (
    SELECT
      Name,
      Course,
      Grade,
      SUM
        (
          CASE
            WHEN Grade <> 'NA' THEN 1
            ELSE 0
          END
        )
        OVER
          (
            PARTITION BY 
              Name
          ) AS num_not_na
    FROM
      myTable
  ) a
WHERE
  a.num_not_na = 0

score 7 · Answer 2 · edited Jan 08 '21 at 09:06

7

Or for fun why not use the ALL syntax

SELECT 
    s1.Name,s1.Course,s1.Grade
FROM dbo.Students s1
WHERE 'NA' = ALL
(
    SELECT a.grade
    FROM dbo.Students a
    WHERE s1.Name = a.Name
)

edited Jan 08 '21 at 09:06

Ash Burlaczenko

193
6

answered Jan 07 '21 at 19:25

Stephen Morris - Mo64

4,656
1
10
18

score 4 · Answer 3 · answered Jan 07 '21 at 17:43

First though is:

SELECT r.[Name], r.Course, r.Grade
  FROM Results AS r
 WHERE NOT EXISTS (
       SELECT * 
         FROM Results AS c
        WHERE c.[Name] = r.[Name]
          AND c.Grade <> 'NA'
            )

Assuming that Grade can not be NULL.

Though that is not at all a normalised table layout, so if it is your own design rather than an example from an early stage of a course then it requires some rethinking.

score 2 · Answer 4 · answered Jan 07 '21 at 19:02

If you only need the name of the student you could also use EXCEPT.

SELECT [Name]
FROM dbo.Students a
EXCEPT
select [Name] 
from dbo.Students
where Grade != 'NA';

Otherwise the NOT EXISTS solution would probably be better:

SELECT 
s1.[Name],s1.Course,s1.Grade
FROM 
dbo.Students s1
INNER JOIN
(
SELECT a.[Name]
FROM dbo.Students a
EXCEPT
select b.[Name] 
from dbo.Students b
where Grade != 'NA') as s2 
on s1.[Name] =s2.[Name];

DB<>Fiddle

Docs on Except

Selecting ALL records when condition is met for ALL records only

4 Answers4