Problem with INNER JOIN on array

[DrDoc]

SELECT this.foo
FROM this
INNER JOIN that ON this.bar IN (that.baz)
WHERE 1

If 'that.baz' is a comma separated list (like '23,54,67,92'), only rows that match the first element in the array ('23') will be returned. However, if I hardcode the array:

SELECT this.foo
FROM this
INNER JOIN that ON this.bar IN (23,54,67,92)
WHERE 1

...then it works as expected. Any ideas?

[DrDoc]

I guess the reason for this is that

... IN (that.baz) ...

is treated not as

... IN (23,54,67,92) ...

but as

... IN ('23,54,67,92') ...

(note single quotes)

Any way around that?

[DrDoc]

I know I can do this:

SELECT this.foo
FROM this
INNER JOIN that ON that.baz
REGEXP CONCAT("^", this.bar, ",¦,", this.bar, ",¦,", this.bar, "$")
WHERE 1

...but that's extremely ugly. Surely there must be a different way?

[coopster]

If this is MySQL you could use the FIND_IN_SET() [dev.mysql.com] function:

SELECT 
 this.foo, 
 FIND_IN_SET(t.id, mytable.services) AS where_found 
 FROM this 
 INNER JOIN that 
 ON (FIND_IN_SET(this.bar, that.baz)) 
;

where_found

will contain the array index of the found value.

[DrDoc]

Hey, you're right! That works like a charm!
:)