CODEWITHSUNDEEP
mysql
scott
scott

Reputation:

Help with a nested query in MySQL

I have a Student_classes table like following:

Student_classes

Student      Classes
 Jon w.      Algebra
 Jon w.      Algebra
 Jake        English
 Scott       Spanish
 Scott       Korean
 Neo         Physics
 Anderson    Chemistry
 Anderson    Psychology

i need to fetch data from this table so that.

  1. if student, classes pair occurs only once, it should show up.
  2. if two rows have same student and classes then one should show up.
  3. if two students have same name but one class is spanish then only one row should be returned and row with 'spanish' should be discarded.
  4. if two rows have same student but different classes and none of them are 'spanish' then both the rows should show.

If all the points are covered then the following should be in the final query:

Jake        English (covers point 1)
Jon w.      Algebra (covers point 2)
Scott       Korean (covers point 3)
Anderson    Chemistry (covers point 4)
Anderson    Psychology (covers point 4)

With the following query I thought i had covered all the basis but looks like it wont cover 4th point. 

SELECT DISTINCT student, classes FROM student_classes
  WHERE classes <> 'Spanish'
  GROUP BY Student;

I have tried to take a subset of a bigger problem i am having.

Can someone please guide me towards coming up with a query that would have all 4 points covered?

I cannot change my db design.

Upvotes: 0

Views: 172

Answers (2)

robmerica
robmerica

Reputation: 6103

Try this:

SELECT DISTINCT student, classes FROM student_classes
WHERE classes <> 'Spanish';

although I suggest you reconsider using the student's names as keys, and rather use a numeric ID/create a students table, since there is no way to differentiate between students with the same name in the same class...

Upvotes: 0

achinda99
achinda99

Reputation: 5078

I think you should get your required result if you remove the GROUP BY

SELECT DISTINCT student,
                classes
FROM            student_classes
WHERE           classes <> 'Spanish'

The DISTINCT takes care of point 1, 2 and 4. The WHERE clause takes care of point 3.

Alternatively, you can group by both:

SELECT DISTINCT student,
                classes
FROM            student_classes
WHERE           classes <> 'Spanish'
GROUP BY        student,
                classes

Upvotes: 1

Related Questions