CSV in Where Condition Mysql

Question

I have two MySQL tables like this:

TableA

 [name]   |   [id]
---------------------
 Shirts      1, 10, 16, 18
 Pants       14, 11
 Skirts      19, 13, 15

TableB

 [id]   |   [s_id]
---------------------
 ABC         1
 AC          1
 DE          10 
 DEC         19
 ACD         16
 BCD         18
 BCO         18

Now I need to get ids from TableB that matches s_id that is from id of TableA for any given name.

The query would look like this:

 SELECT id 
   FROM TableB
  WHERE s_id IN ( SELECT id
                    FROM TableA
                   WHERE name = 'Shirts' )

So the sub-query returns 1, 10, 16, 18 (csv). But I know this cannot be used like this in the sub-query.

Any ideas?

What you have is a really bad schema. Since I don't really understand what those id's in the table actually represent it is hard for me to give you an answer on how to fix the schema. Can you explain, in real-world terms, what the tables represent so a suggestion on how to fix your schema can be given? — Mike Brant, Dec 28 '12 at 18:38
I understand that the schema is in bad shape. But unfortunately I cannot change the schema since its an existing live product developed a while ago. — Kevin Rave, Dec 28 '12 at 18:40
It always amazes me how people asking these questions oftentimes say "I can't change the schema". Sometimes in software development schemas need to be changed, especially in cases where you are trying to query the data in ways you have not done before. — Mike Brant, Dec 28 '12 at 18:45
@MikeBrant I get your point :-). I am working on API for the existing software, which has been developed for 5 years. Its not a simple product. It has ton of files and ton of tables. A small change can affect BIG. Though I would love to fix this crap, I cannot. :-) — Kevin Rave, Dec 28 '12 at 18:47

Saharsh Shah · Answer 1 · 2014-12-09T06:08:13.410

2

Try this:

SELECT b.id, b.s_id
FROM TableB b 
INNER JOIN TableA a ON FIND_IN_SET(b.s_id, REPLACE(a.id, ' ', ''))
WHERE a.name = 'Shirts';

edited Dec 09 '14 at 06:08

answered Dec 28 '12 at 18:41

Saharsh Shah

28,687
8
48
83

There are two problems with this. First is that comma-delimited values also have spaces after the commas which confounds FIND_IN_SET(). Second is that FIND_IN_SET() returns the index of the found value within the set, not the value itself. Try it: select find_in_set('16', '1,10,16,18') – Slippery Pete Dec 28 '12 at 18:51
@SlipperyPete: the second problem isn't really a problem. If the expr1 is "found in set" expr2, then the return value is a positive integer, which evaluates to TRUE in boolean context. Another big problem with this is going to be performance. – spencer7593 Dec 28 '12 at 18:57
@SlipperyPete We can use REPLACE keyword to remove the space check my updated answer. And second answer is we want to check weather that id exists in that string and it will done by FIND_IN_SET. – Saharsh Shah Dec 28 '12 at 18:57
@SaharshShah Correct on both counts, I just came to the same conclusion. Thanks - – Slippery Pete Dec 28 '12 at 19:01
I guess this is going to be a BIG performance hog. Will try this anyway. – Kevin Rave Dec 28 '12 at 19:03
@KevinRave Try this in two way with REPLACE function and without REPLACE function. – Saharsh Shah Dec 28 '12 at 19:04
@KevinRave This query won't work if inner query returns multiple rows, it rows **subquery returns more than 1 row**. And it also slow doen the performance. Check my query it will work for multiple rows from TableA and also improve performance – Saharsh Shah Dec 29 '12 at 05:12

score 1 · Accepted Answer · answered Dec 28 '12 at 18:48

1

You might give this a try. This query will require a full table scan, so I hope your table isn't large.

SELECT id
FROM TableB
WHERE FIND_IN_SET(s_id, (SELECT id FROM TableA WHERE name = 'Shirts')) IS NOT NULL

answered Dec 28 '12 at 18:48

Mike Brant

70,514
10
99
103

CSV in Where Condition Mysql

2 Answers2