patternsqlMinor

Selecting ALL records when condition is met for ALL records only

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

metconditionallrecordsforselectingwhenonly

Problem

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

Solution

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

Code Snippets

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

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

Context

StackExchange Database Administrators Q#282766, answer score: 8

Revisions (0)

No revisions yet.