CODEWITHSUNDEEP
mysqlsqlswitch-statement
Drek
Drek

Reputation: 87

MySQL case-when-then (or other ways) to check values of multiple rows

I request some help with MySQL when-then statement to fetch all the sid from the table after comparing multiple records having same sid but different cid-data values:

flag    sid cid data
----    --- --- ----
1       300 1   john
1       300 2   john_email
1       300 3   77500
1       300 4   ok

1       301 1   jack
1       301 2   john_email
1       301 3   72210
1       301 4   notok

Here for each sid, I need to check if (sid=2 has data=john_email) AND (sid=4 has data=ok) Only if both the conditions are satisfied, I return the sid. i.e. the output will be '300' only.

I am confused how to use the case-when-then and compare 'data' with 'john_email' and also compare data with 'ok' ... based on the cid values. Thanks for reading.

Upvotes: 1

Views: 216

Answers (3)

juergen d
juergen d

Reputation: 204894

try

select sid
from your_table
group by sid
where (cid=2 and data='john_email')
or (cid=4 and data='ok')
having sum(cid=2)=1 and sum(data='john_email')=1
and sum(cid=4)=1 and sum(data='ok')=1

SQLFiddle example

Upvotes: 2

Kao
Kao

Reputation: 2272

What you can do is use a subquery and check if the value exists.

SELECT
    *
FROM
    table outertable
WHERE
    ( cid=2 AND data='john_email' )
    AND
    EXISTS ( SELECT sid FROM table WHERE cid = 4 AND data = 'ok' AND sid = outertable.sid )

Upvotes: 0

ppeterka
ppeterka

Reputation: 20726

You should join the table to itself, then you can check the condition in the two rows as if it was one row... 

SELECT T1.sid
FROM MYTABLE T1
JOIN MYTABLE T2 ON T1.SID=T2.SID AND T1.CID=1 AND T2.CID=4
WHERE T1.DATA='john'
AND T2.DATA='ok'

Note that I used the CID values in the join clause, but you will have to adjust them if you want to join on different data rows...

Upvotes: 0

Related Questions