CODEWITHSUNDEEP
mysqlsqlselect
Iftieaq
Iftieaq

Reputation: 1964

MYSQL - Multiple and condition on different values on same column

I have a table name results

+----+------+------+--------+
| ID | USER | EXAM | RESULT |
+----+------+------+--------+
|  1 |   10 | ssc  | 4.2    |
|  2 |   10 | hsc  | 4.5    |
|  3 |   11 | ssc  | 4.0    |
|  4 |   11 | hsc  | 4.4    |
|  5 |   12 | ssc  | 5.0    |
|  6 |   12 | hsc  | 5.0    |
|  7 |   13 | ssc  | 3.0    |
|  8 |   13 | hsc  | 3.0    |
+----+------+------+--------+

My goal is to retrieve distinct users having a particular score. For example ssc result > 4.0 and hsc result > 4.2.

If I put something like this

select distinct user from results where 
( exam = 'ssc' and result > 4.0 ) 
and 
( exam = 'hsc' and result > 4.2 )

It will return empty as exam cannot equal to 'ssc' and 'hsc' at the same time. If I put or condition in the middle like this

select distinct user from results where 
( exam = 'ssc' and result > 4.0 ) 
or
( exam = 'hsc' and result > 4.2 )

It will include user 11 which is unexpected because our target is to filter the users having greater than 4.0 in ssc.

My expected output will be something like this

10
12

Is there any way we can do this?

Upvotes: 1

Views: 1909

Answers (3)

D-Shih
D-Shih

Reputation: 46219

You can try to use condition aggregate function in HAVING and check both conditions are established.

CREATE TABLE T (
   ID INT,
   `USER` INT,
   EXAM VARCHAR(50),
   RESULT FLOAT
);

INSERT INTO T VALUES (1,10,'ssc', 4.2);
INSERT INTO T VALUES (2,10,'hsc', 4.5);
INSERT INTO T VALUES (3,11,'ssc', 4.0);
INSERT INTO T VALUES (4,11,'hsc', 4.4);
INSERT INTO T VALUES (5,12,'ssc', 5.0);
INSERT INTO T VALUES (6,12,'hsc', 5.0);
INSERT INTO T VALUES (7,13,'ssc', 3.0);
INSERT INTO T VALUES (8,13,'hsc', 3.0);

Query 1:

SELECT USER  
FROM T 
GROUP BY USER 
HAVING 
    SUM(exam = 'ssc' and result > 4.0) = 1 
AND 
    SUM(exam = 'hsc' and result > 4.2) = 1

Results:

| USER |
|------|
|   10 |
|   12 |

Upvotes: 4

Fahmi
Fahmi

Reputation: 37473

use conditional aggregation for filter out

select user from tablename
group by user
having 
sum(case when exam='ssc' and result>4.0 then 2 when exam='hsc' and result>4.2 then 1 end)=3

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

one method using join

  select t1.* from 
(
select * from results where EXAM ='ssc' and RESULT>4.0
    ) t1
  join
(
    select * from  results where EXAM ='hsc' and RESULT>4.2
) as t2 on t1.USER=t2.USER

Upvotes: 1

Related Questions