CODEWITHSUNDEEP
phpmysql
Shameem
Shameem

Reputation: 317

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)

Upvotes: 1

Views: 58

Answers (3)

kaushik karan
kaushik karan

Reputation: 361

Try this one:

select 
    s.name
from
    Students s,
    Categgory c,
    student_category sc
where
    sc.student_id = s.id
        and sc.category_id = c.id
        and c.id = 2
        and c.id = 4

You can check it on SQL Fiddle. Have to take distinct student name as it will repeat if a student falls in more than one category.

Upvotes: 0

Denny Sutedja
Denny Sutedja

Reputation: 538

try this :

select name from Students where id in (select student_id from student_category where category_id in (2,4))

your query is fine btw.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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

Upvotes: 3

Related Questions