Selecting ALL records when condition is met for ALL records only 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
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 &lt;&gt; 'NA' 
    )

Somewhat less conventional but potentially more efficient:
SELECT
 Name,
 Course,
 Grade
FROM
  (
    SELECT
      Name,
      Course,
      Grade,
      SUM
        (
          CASE
            WHEN Grade &lt;&gt; 'NA' THEN 1
            ELSE 0
          END
        )
        OVER
          (
            PARTITION BY 
              Name
          ) AS num_not_na
    FROM
      myTable
  ) a
WHERE
  a.num_not_na = 0