具有多个条件的MySql查询

Question

I have two tables

1) Student

 id   |   Student_name
--------------------
 1    |   John
 2    |   Joy
 3    |   Raju

2) Category

 id    |  category_name
-------------------------
 1     |  Maths Fest
 2     |  Science Fest
 3     |  IT Fest
 4     |  English Fest
 5     |  Cultural Fest

3) Student_category

  id   | student_id  | category_id
 ------------------------------------
  1    |    1        |     4
  2    |    1        |     5
  3    |    1        |     1
  4    |    2        |     1
  5    |    2        |     4
  6    |    3        |     1
  7    |    3        |     5
  8    |    3        |     3

I need to write a query to select students who have participate in both Maths fest & English Fest.

i used this query

SELECT distinct student_name 
FROM student A,student_category B 
WHERE A.id=B.student_id 
  and B.category_id IN ('1','4')

but it give result student who participate in Maths fest OR English Fest. please help me

Answer 1

If you have to have two different categories, you could simply join twice:

SELECT student_name 
FROM student A
  INNER JOIN student_category B ON A.id=B.student_id AND B.category_id = 1
  INNER JOIN student_category C ON A.id=C.student_id AND C.category_id = 4

That way you'll get the students for which both joins are existing

For dynamic selection of categories (more than 2, if you know the amount and join table contains no duplicates) you could do

SELECT student_name
  FROM student A 
    INNER JOIN student_category B on A.id = B.student_id 
        AND B.category IN (1,4,5) -- one more
  GROUP BY student_name 
  HAVING count(*) = 3 -- Number of categories in IN clause