can i use IN operator in MYSQ ON Statement

Question

I have this sample mysql statement

SELECT f.* FROM foo f
INNER JOIN bar b ON b.id IN(f.field_containing_values_separated_by_comma)
WHERE b.id IN (1,2,3)

sample values of (varchar)f.field_containing_values_separated_by_comma would be

'1,3,5'
'2,5,7'
'10,9'
'4,5'

my expected results would be to fetch the foo records of this f.field_containing_values_separated_by_comma

'1,3,5'
'2,5,7'

any ideas why im not getting it? or where there be any other better ways of doing this? im using PHP as back end by the way

Regards

Have a read of https://stackoverflow.com/questions/4155873/find-in-set-vs-in — Nigel Ren, Jun 19 '19 at 15:55

score 0 · Answer 1 · answered Jun 19 '19 at 16:16

0

I believe you need to use the FIND_IN_SET function like this:

select distinct foo.* from foo cross join bar
WHERE bar.id IN(1,2) and FIND_IN_SET(foo.id, bar.field_containing_values_separated_by_comma)

Documention here

answered Jun 19 '19 at 16:16

Eduardo Silva

1 Answers1