MySQL - cast columns to array and use IN statement on it

Question

I have table Foo like:

id,
bar_1,
bar_2,
bar_3

bar1, bar2, bar3 could contain foreign key (integer) or null. I want to select all rows from Foo, where ids 2, 4 (both) are present in any two of bar1, bar2, bar3.

Simple way would be to make lot of OR's, but I believe there's simplier way.

I thought about something like

SELECT * FROM foo WHERE (2,4) IN ARRAY(bar_1, bar_2, bar_3)

Is it possible?

You misunderstood my question. (2, 4) should be present in any subset of bar_1, bar_2, bar_3, for example bar_1 = 2 and bar_2 = 4 or bar_1 = 4 and bar_2 = 2 or bar_1 = 4 and bar_3 = 2 and so on (cartesian) — ex3v
– ex3v, Commented Apr 17, 2015 at 13:18

Thorsten Kettner · Accepted Answer · 2015-04-17 13:17:49Z

3

Well, almost :-)

SELECT * FROM foo 
WHERE 2 IN (bar_1, bar_2, bar_3)
  AND 4 IN (bar_1, bar_2, bar_3);

answered Apr 17, 2015 at 13:17

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

Comments

Florian D · Accepted Answer · 2015-04-17 13:16:13Z

0

SELECT * FROM foo WHERE bar_1 IN (2,4) OR bar_2 IN (2,4) OR bar_3 IN (2,4)

answered Apr 17, 2015 at 13:16

Florian D

561 silver badge9 bronze badges

3 Comments

Arsen Mkrtchyan Over a year ago

what if bar_1 = 2, bar_2 = 2 and bar_3 = 2?

Arsen Mkrtchyan Over a year ago

both 2 and 4 should exists... in this case only 2 exists

Florian D Over a year ago

uups, yes you right. @ThorstenKettner has the write answer isn't it

lakshman · Accepted Answer · 2015-04-17 13:21:10Z

0

I think..no.. use code like this

select * from foo where ((concat(bar_1,',',bar_2)='2,4') || (concat(bar_2,',',bar_3)='2,4') || (concat(bar_3,',',bar_1)='2,4') || (concat(bar_2,',',bar_1)='2,4') || (concat(bar_3,',',bar_2)='2,4') || (concat(bar_1,',',bar_3)='2,4'))

answered Apr 17, 2015 at 13:21

lakshman

6574 silver badges18 bronze badges

4 Comments

Arsen Mkrtchyan Over a year ago

what if bar_1 = 4, bar_2 = 2 ?

lakshman Over a year ago

Please check the query again..the condition has been concatenated as bar_2,',',bar_1='2,4'

lakshman Over a year ago

if it fulfills the answer then ok... please explain mess by concatenation after that concatenation will be removed from MySQL

Arsen Mkrtchyan Over a year ago

any reason to concat, and do additional operations, when you can just compare?

Collectives™ on Stack Overflow

MySQL - cast columns to array and use IN statement on it

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related