How to not inculde 2 and 2 in sql with subquery?-CodePudding

Trying to take results who has -1 and 2 OR 2 and-1 OR -1 and-1 BUT NOT 2 and 2. It so confused me

SELECT   c.studentId,  c.Name 
FROM  Classes c 
WHERE c.Class = 'Math' 
AND c.Grade IN ('-1')      // if here (-1)  OR (2) OR (-1)
AND c.studentId IN (  
   SELECT c2.studentId  
   FROM    Classes c2  
   WHERE c2.Class = 'Fiz' 
   AND c2.Grade IN  ('2'))  // then here(2)  OR (-1) OR (-1)

If I write in both Grade IN (-1,2) it will also include who has 2 and 2 which I dont want in my case

CodePudding user response：

You can self-join Classes and the filter out students that have both 2 for fiz and math.

You didn't provide us with test data to play with, so I couldn't test it propertly

SELECT   c.studentId,  c.Name 
  FROM Classes c_math,
  JOIN Classes c_fiz
    ON c_math.studentId = c_fiz.studentId
 WHERE c_math.Class = 'Math' 
   and c_fiz.Class = 'Fiz' 
   AND c_math.Grade IN ('-1', '2')
   AND c_fiz.Grade IN ('-1', '2')
   and not (c_math.grade = '2' and c_fiz.grade = '2')

CodePudding user response：

Get all the records that can match, then aggregate them all by student. At that point you can use HAVING to assert conditions on the set of rows being aggregated for each student.

For example, you can say that at least one row has to have a grade of -1.

SELECT
  c.studentId,  c.Name 
FROM
  Classes c 
WHERE
      c.Class IN ('Math', 'Fiz')
  AND c.Grade IN (-1, 2)
GROUP BY
  c.studentId,  c.Name 
HAVING
  MIN(c.Grade) = -1

You could also add AND COUNT(DISTINCT c.Class) = 2 to be sure that you get at least two different classes, depending on your data, requirements, etc.

Note: Assumes that one student can't have two different grade entries for the same class.

CodePudding user response：

SELECT      c1.studentId,  c1.Name 
FROM        Classes c1 
Inner Join  Classes c2
on          c1.studentId = c2.studentId
WHERE       c1.Class = 'Math'
and         c2.Class = 'Fiz'
and       (( c2.Grade = 2 and c1.Grade <> 2 )
or         ( c2.Grade <> 2 and c1.Grade = 2 ) )