MySQL selecting values based on multiple rows

Question

I have a MySQL table like so;

foo      bar
1        21
23       17
31       17
23       21
19       9
23       4
31       4
3        27
51       6
31       44
23       44
31       71

What I want is to select all the unique values of bar that correspond to both 23 and 31 in the foocolumn.

i.e. for this table, I'd get the following result;

bar
17
4
44

17, 4, and 44 all get selected, since for all of those values, two rows exist that correspond to both 23 and 31 in the foo column.

Even though the values 21 and 71 in bar correspond to 23 and 31 in foo, they would not get selected, since the same value of bar isn't present on another row that would correspond to the other number.

How would I go about this?

Are the combinations of foo and bar unique? (i.e. 23 -> 17 won't appear twice in the table) — Zane Bien
– Zane Bien, Commented Jun 17, 2012 at 19:51

Zane Bien · Accepted Answer · 2012-06-17 19:17:22Z

2

SELECT 
    bar
FROM 
    tbl
WHERE
    foo IN (23,31)
GROUP BY
    bar
HAVING
    COUNT(*) = 2

The 2 in COUNT(*) represents the amount of values of foo you're checking on — in this case, two. If you wanted bars corresponding to all of (23,31,7) for example, change it to COUNT(*) = 3 because there are three values to satisfy.

answered Jun 17, 2012 at 19:17

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sashi Kant · Accepted Answer · 2012-06-17 19:18:22Z

1

Try this ::

Select tab1.bar
from
(select bar from table where foo=/*?(say 23)*/) tab1
inner join 
(Select bar from table where foo=/*?(say 31)*/) tab2
on (tab1.bar=tab2.bar)

answered Jun 17, 2012 at 19:18

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Comments

Fahim Parkar · Accepted Answer · 2012-06-17 19:20:16Z

1

How about this?

SELECT bar
FROM mytable
WHERE foo IN (23,31)
GROUP BY bar
HAVING count(bar) >= 2

Demo

answered Jun 17, 2012 at 19:20

Fahim Parkar

31.8k46 gold badges168 silver badges285 bronze badges

2 Comments

Zane Bien Over a year ago

Just FYI, the > is unnecessary since there will be at most, two values for bar if the combinations are unique.

Fahim Parkar Over a year ago

@ZaneBien : I have used > incase there are cases where count is more than or equal to 2.

Collectives™ on Stack Overflow

MySQL selecting values based on multiple rows

3 Answers 3

Comments

Comments

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related