Find records as And in MYSQL

Farzad
July 27, 2024
64 views
1 vote
2 Answers

I want find Students id that , must have (professorId = 3 And courseId = 4) And (professorId = 3 And courseId = 2) And (professorId = 5 And courseId = 8).
sometime these parenthesis is one or more than 20

I have two table class and studentsClasses like this :

studentsClasses table:

id | studentId | classId
1        1         2
2        1         3
3        1         4
4        2         3
5        2         5
6        3         2
7        4         3
8        4         2
9        1         8

Class Table:

id | professorId | CourseId

1        1            1
2        3            4
3        2            5
4        3            2
5        4            2
6        2            3
7        5            6
8        5            8
9        5            9

It would be very simple if every parenthesis worked as or. But I want to find a student who has all these things in the form of and.

This query is "OR" scenario:

select `studentsClasses`.`studentId` from `studentsClasses`
 inner join `class` on (`studentsClasses`.`classId`=`class`.`id` 
AND ((professorId = 3 And courseId = 4) OR (professorId = 3 And courseId = 2) 
   OR (professorId = 5 And courseId = 8)))

But i want AND scenario like this:

select `studentsClasses`.`studentId` from `studentsClasses`
     inner join `class` on (`studentsClasses`.`classId`=`class`.`id` 
    AND ((professorId = 3 And courseId = 4) AND (professorId = 3 And courseId = 2) 
       AND (professorId = 5 And courseId = 8)))

But above query is wrong.

For example result query with these value should be :

studentId
---------
1

Tags: mysql sql

Answers

One way is to use CASE WHEN condition and SUM over it.

SELECT
  studentId,
  SUM(
    CASE
      WHEN c.professorId = 3 AND c.courseId = 4 THEN 1
      WHEN c.professorId = 3 AND c.courseId = 2 THEN 1
      WHEN c.professorId = 5 AND c.courseId = 8 THEN 1
      ELSE 0
    END) AS filter_cnt
FROM studentsClasses s
JOIN class c ON c.id = s.classId
GROUP BY 1
HAVING filter_cnt = 3

Another way is to use a separate join for each condition. Not an efficient solution, but it might be fine if data is not too big.

WITH class_1 AS (
  SELECT DISTINCT studentId FROM studentsClasses s
  JOIN class c ON c.id = s.classId AND c.professorId = 3 AND c.courseId = 4
),

class_2 AS (
  SELECT DISTINCT studentId FROM studentsClasses s
  JOIN class c ON c.id = s.classId AND c.professorId = 3 AND c.courseId = 2
),

class_3 AS (
  SELECT DISTINCT studentId FROM studentsClasses s
  JOIN class c ON c.id = s.classId AND c.professorId = 5 AND c.courseId = 8
)

SELECT c1.studentId FROM class_1 c1
JOIN class_2 c2 ON c2.studentId = c1.studentId
JOIN class_3 c3 ON c3.studentId = c1.studentId

One option is to use IN operator on pair of columns, group by student HAVING Count() = Number of pairs listed – 3 in this sample and up to 20 or whatever it is in your actual context…

WITH    --  S a m p l e    D a t a :
  studentsClasses ( id, studentId, classId ) AS
    ( Select 1, 1, 2 Union All
      Select 2, 1, 3 Union All
      Select 3, 1, 4 Union All
      Select 4, 2, 3 Union All
      Select 5, 2, 5 Union All
      Select 6, 3, 2 Union All
      Select 7, 4, 3 Union All
      Select 8, 4, 2 Union All
      Select 9, 1, 8
    ),
  Class ( id, professorId, CourseId ) AS
    ( Select 1, 1, 1 Union All
      Select 2, 3, 4 Union All
      Select 3, 2, 5 Union All
      Select 4, 3, 2 Union All
      Select 5, 4, 2 Union All
      Select 6, 2, 3 Union All
      Select 7, 5, 6 Union All
      Select 8, 5, 8 Union All
      Select 9, 5, 9
    )

--    S Q L :
Select     s.studentId
From       studentsClasses s
Inner Join Class c ON c.id = s.classId
Where      (c.professorId, c.CourseId) IN( (3, 4), (3, 2), (5, 8) )
Group By   1
Having     Count(s.studentId) = 3  -- 3 is number of pairs matched

/*  R e s u l t :
studentId
---------
        1    */

See the fiddle here.

Please signup or login to give your own answer.

Click here to cancel reply.