6

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

1
  • This type of problem is called Relational division, and I've added a corresponding tag to your question. – Andriy M Jan 8 at 10:38
9

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
0
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
)
4

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.

2
  • That table is normalized assuming the primary key is (Name,Course), although outside of the question we might replace Name with StudentId and Course with something like CourseCode. – bbaird Jan 7 at 18:59
  • @bbaird - yes, I didn't assume the extra step. If the sample data is from a view based on a student/course junction table, not just a table with those values in, then it would probably be part of a normalised structure. – David Spillett Jan 8 at 10:00
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.