MySQL query that find matches that are ecual

Question

I have the following MySQL table called "MyTable"

ID--- a --- b
10---20---42
11---20---43
12---20---44
13---21---44
14---20---45
14---22---44
14---22---43
14---24---45
14---22---42

I am trying to write a query that gets me into the following results. Examples.

SELECT b FROM mytable WHERE a=(20, 21, 22)

This returns 44 as it is the only number that is common.

SELECT b FROM mytable WHERE a=(20, 22) AND b is the same

This returns 42 and also 43 because both match

SELECT b FROM mytable WHERE a=(22, 24) AND b is the same

This returns nothing because the b value is different between the numbers in column a.

Thanks for your help

(found an answer I guess)

 
select b 
from MyTable 
group by b 
having sum(a = 20) > 0 and // -- has id = 20 
sum(a = 21) > 0 // -- has id = 21

select b from MyTable group by b having sum(a = 20) > 0 and -- has id = 2 sum(a = 21) > 0 -- has id = 4 — Thanasis, Sep 26 '16 at 17:04
Ok Barmar. I think I got the answer from the post you linked. I even edited my original post to include the answer — Thanasis, Sep 26 '16 at 17:10

score 0 · Answer 1 · answered Sep 26 '16 at 16:07

For queries like that where you want to narrow down by a set you can do it one of two ways, the long way:

SELECT b FROM mytable WHERE a=20 OR a=21 OR a=22

Or the short way, by using the IN keyword:

SELECT b FROM mytable WHERE a IN (20, 21, 22)

for your query 'SELECT b FROM mytable WHERE a=(20, 22) AND b is the same' you can still use the same idea, but do:

SELECT b FROM mytable WHERE a IN(20, 22) AND b=a

1 Answers1