Shameem Shameem - 3 months ago 7
MySQL Question

Mysql IN query instead of multiple AND conditon

I have three mysql tables, category,students and student_category. for each student there is 1 or more category will be there and it is stored in student_category as follows.

1) Categgory

----------------------------
id | category_name
---------------------------
1 | A
2 | B
3 | C
4 | D


2) Students

--------------------------
id | name
--------------------------
1 | John
2 | Kumar
3 | Ashok
4 | Jorge
5 | Suku
-------------------------


2) student_category

-----------------------------------------
id | student_id | category_id
-----------------------------------------
1 | 1 | 2
2 | 1 | 4
3 | 2 | 3
4 | 2 | 1
5 | 3 | 2
------------------------------------------


I need to select students which contain category_id 2 and 4.

i used query as follows but it return either students contain category 2 or category 4.

select A.name from students A, student_category B where A.id=B.student_id
and B.category_id IN (2,4)

Answer

Try this query:

SELECT t1.id,
       t3.name
FROM students t1
INNER JOIN student_category t2
    ON t1.id = t2.student_id
INNER JOIN students t3
    ON t1.id = t3.id
WHERE t2.category_id IN (2, 4)
GROUP BY t1.id
HAVING COUNT(DISTINCT t2.category_id) = 2

Explanation:

This query joins together the students and student_category tables, and then removes all records which are not category 2 or 4. This means that each student would then only have category 2 and 4 records associated with him. The HAVING clause then restricts further by requiring that a student have two distinct categories, which if true must mean that the student has both category 2 and 4.

Demo here:

SQLFiddle