MySql Query with multiple condition

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

Jan · Accepted Answer · 2016-11-15 10:51:31Z

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

edited Nov 15, 2016 at 10:51

answered Nov 15, 2016 at 8:56

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shameem Over a year ago

This is ok, but if more categories, then practically it is not possible, I need to generate this query dynamically.

Shashikala Over a year ago

@jan some corrections : SELECT student_name FROM student A INNER JOIN student_category B ON A.id = B.student_id AND B.category_id IN ( 1, 4 ) GROUP BY student_name HAVING count( * ) =2

Jan Over a year ago

@Webbie, second SQL is for extended request as stated by Shameem

Danila Ganchar · Accepted Answer · 2016-11-15 15:10:57Z

0

Try this:

SELECT student_name
  FROM student A 
 INNER JOIN student_category B 
       ON A.id = B.student_id AND B.category_id IN ( 1, 4 ) 
 GROUP BY student_name HAVING count( * ) = 2

This query will only return student name when the count of that student name is twice. Once for English Fest and once for Math Fest.

If there are more categories then you can simply count how many categories are there in your comma separated string and replace count(*) = 2 with count(*) = no. of categories.

Example to check students who have participated in all the categories or more than 2 category:

$category_id = 1, 2, 3, 4, 5  
$a = substr_count($category_id, ","); // this will count number of times comma is appearing in your string.
$a = $a + 1;                         // number of items is + 1 than number of commas.

Query looks like below:

SELECT A.student_name 
  FROM student A,
       student_category B   
 WHERE A.id = B.student_id AND B.category_id IN ('1', '4')  
HAVING count(*) = $a;

Hope it helps.

edited Nov 15, 2016 at 15:10

Danila Ganchar

11.5k13 gold badges69 silver badges102 bronze badges

answered Nov 15, 2016 at 8:55

Milan Gupta

1,1818 silver badges21 bronze badges

2 Comments

Jan Over a year ago

a group by might be more clean if you use HAVING clause

Milan Gupta Over a year ago

Not sure how GROUP BY would fit in here?

Collectives™ on Stack Overflow

MySql Query with multiple condition

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related