CODEWITHSUNDEEP
mysqllaravel-5
tyro
tyro

Reputation: 1428

Multiple where in clause in Mysql

When using the following query Im getting the incorrect results. How can I fix this when working with multiple where in clauses and HAVING.

SELECT * 
FROM  `otc_employee_qualifications` 
WHERE  `emp_qualifctn_type` 
IN (
'26',  '27'
)
AND  `qualification_value` 
IN (
'62', '64','65'
)
AND  `qualification_mark` >=  '10'
GROUP BY  `employee_id` 
HAVING COUNT( DISTINCT  `emp_qualifctn_type` ) =  '2'
AND  COUNT( DISTINCT  `qualification_value` ) =  '3'
LIMIT 0 , 30

PHPMYADMIN

enter image description here

Here I need to get the employee ids who satisfies both the emp_qualifctn_type (27 and 26) and all qualification values in ( '62', '64','65')

Upvotes: 2

Views: 1564

Answers (3)

tyro
tyro

Reputation: 1428

I found the mistake in the query. I should use 'OR' in the query for combining the multiple cases in HAVING clause.

SELECT *
FROM  `otc_employee_qualifications` 
WHERE  `emp_qualifctn_type` IN ('26',  '27')
AND  `qualification_value` IN ('62', '64','65')
AND  `qualification_mark` >=  '10'
GROUP BY  `employee_id` 
HAVING COUNT( DISTINCT  `emp_qualifctn_type` ) =  '2'
OR  COUNT( DISTINCT  `qualification_value` ) =  '3'

Here the result will be Null which is true.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

With the data above no employees satisfy all the values for both for your comment I want to get all employee_ids say, who have both emp_qualifctn_type (27 , 29) and both qualification_value (62,50).

For example for 2, the first rows has a match on both emp_qualifctn_type and qualification_value, but the 2nd row only matches on emp_qualifctn_type while the 3rd only matches on qualification_value. So 2 does not have a match on all of them.

To get what you want I think you need to match on one OR the other rather than AND, and then you can check the counts:-

SELECT employee_id 
FROM  `otc_employee_qualifications` 
WHERE  (`emp_qualifctn_type` IN (27,  29)
OR  `qualification_value` IN (50, 62))
AND  `qualification_mark` >=  '10'
GROUP BY  `employee_id` 
HAVING COUNT( DISTINCT  `emp_qualifctn_type` ) =  2
AND  COUNT( DISTINCT  `qualification_value` ) =  2

Upvotes: 0

Somnath Muluk
Somnath Muluk

Reputation: 57656

For your updated comment I want to get all employee_ids say, who have both emp_qualifctn_type (27 , 29) and both qualification_value (62,50). Here the result would be 2.. Check this =>

SELECT * 
FROM  `otc_employee_qualifications` 
WHERE  `emp_qualifctn_type` 
IN (27,  29)
AND  `qualification_value` 
IN (62, 50)
AND  `qualification_mark` >=  10
GROUP BY  `employee_id`
HAVING count(DISTINCT emp_qualifctn_type) >= 2 
LIMIT 0 , 30

Upvotes: 0

Related Questions