php - Mysql IN query instead of multiple AND conditon -

i have 3 mysql tables, category,students , student_category. each student there 1 or more category there , stored in student_category follows.

1)   categgory     ----------------------------     id      |  category_name    ---------------------------     1       |       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 select students contain category_id 2 , 4.

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

   select a.name students a, student_category b a.id=b.student_id    , b.category_id in (2,4)  

try query:

select t1.id,        t3.name students t1 inner join student_category t2     on t1.id = t2.student_id inner join students t3     on t1.id = t3.id t2.category_id in (2, 4) group t1.id having count(distinct t2.category_id) = 2 

explanation:

this query joins students , student_category tables, , removes records not category 2 or 4. means each student have category 2 , 4 records associated him. having clause restricts further requiring student have two distinct categories, if true must mean student has both category 2 , 4.

demo here:

sqlfiddle