Shameem Shameem - 21 days ago 6
MySQL Question

MySql Query with multiple condition

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

Jan Jan
Answer

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