How to use 'IN' statement in mySQL cascaded SELECT statement?

Question

I have two tables A and B. I need to pull data from them in a combined statement using GROUP_CONCAT. Currently, it does not work as I expected. So I separated the statement into two seperated simple statements to test.

This is how I did.

Tabel A
FIELD1 FIELD
1      A  
2      B
3      C

Table B
FIELD1  FIELD2
1       1,2
2       1
3       2,3

I can read the Tabel B as follows

SELECT B.FIELD2 FROM B WHERE FIELD1=3

Output is

FIELD2
2,3

Now if I read Tabel A as follows

SELECT GROUP_CONCAT(A.FIELD2) FROM A WHERE FIELD1 IN (2,3)

I get

FIELD2
B,C

Now I want to get the same output with the following statement but it fails.

SELECT GROUP_CONCAT(A.FIELD2) FROM A WHERE FIELD1 IN (SELECT B.FIELD2 FROM B WHERE FIELD1=3)

Any help fixing the statement?

Answer 1

This is slightly difficult to explain. The IN operator doesn't look at a CSV string in a column and treat it like a comma separated list of values to be looked through. If a column value is some CSV data then the in operator will look for that exact string, commas and everything, in some other list of values

2 IN (1,2) --true
'2' IN ('1','2') --true
'2' IN ('1,2') --false 
'1,2' IN ('1,2') --true

Always remember that your SQL is compiled like any other program. Data that you write into the statement becomes part of the program. Data in the table does NOT become part of the program. This is why you can't use IN on some comma separated list of stuff you find in a table row

As it stands you'll have to split the '2,3' to two rows of '2' and '3', or keep it as a string and use like

SELECT GROUP_CONCAT(A.FIELD2) 
FROM A
  INNER JOIN B 
  ON CONCAT(',', B.FIELD2, ',') LIKE CONCAT('%,', A.FIELD1, ',%')
WHERE B.FIELD1=3

Nasty! :)

Answer 2

You can'd do it with the IN operator because B.FIELD2 is not a list of values (which is what IN expects) but a string.
You must join the tables and use FIND_IN_SET():

SELECT GROUP_CONCAT(A.FIELD2) FIELD2
FROM A INNER JOIN B
ON FIND_IN_SET(A.FIELD1, B.FIELD2)
WHERE B.FIELD1 = 3;

See the demo.
Results:

| FIELD2 |
| ------ |
| B,C    |