CODEWITHSUNDEEP
phpmysql
Shameem
Shameem

Reputation: 317

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

Upvotes: 0

Views: 72

Answers (2)

Milan Gupta
Milan Gupta

Reputation: 1181

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.

Upvotes: 0

Jan
Jan

Reputation: 13858

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

Upvotes: 1

Related Questions