MySQL IN problem

Question

Hi guys,

I'm trying to use the value of another field inside an IN.

SELECT types.id, title, auth_users.types
FROM types
LEFT JOIN auth_users ON auth_users.id IN (8,9)
WHERE types.id IN (1,2,3)
GROUP BY types.id

Works

SELECT types.id, title, auth_users.types
FROM types
LEFT JOIN auth_users ON auth_users.id IN (8,9)
WHERE types.id IN (auth_users.types)
GROUP BY types.id

Does not work

http://pastebin.com/m76ae0596 More info can be found here

auth_users.types = 1,2,3

Answer 1

Try row subqueries:

 SELECT column1,column2,column3
   FROM t1
   WHERE (column1,column2,column3) IN
         (SELECT column1,column2,column3 FROM t2);

EDIT:

Actually, looking at your pastebin, are you storing "1,2,3" in a single row as a varchar? That's not going to work.

Answer 2

Unfortunately, IN (...) won't attempt to parse a varchar-field value to extract the values you want to match against.

It basically just checks if "types.id" is one of "1, 2, 3" (ie. not 1, 2 or 3, but the actual single value "1, 2, 3".)

One way to solve this would be to have a function in the database that returns a resultset, and takes that varchar value in, parses it, and returns 3 rows for your example.

Other than that, you need to either rewrite to use LIKE (which will perform horribly), or you need to parse those values yourself and place the result into the SQL directly.

Answer 3

You need to use FIND_IN_SET - see this question: http://stackoverflow.com/questions/834074/mysql-select-statement-using-in-operator

Answer 4

I guess he is looking for a simple join

LEFT JOIN auth_users ON types.id = auth_users.types

WHERE auth_users.id IN (8,9)