Sql-server – Selecting ALL records when condition is met for ALL records only

relational-divisionsql serversql-server-2012

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

Best Answer

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
Related Question