CODEWITHSUNDEEP
mysqlselectcsvwhere-clause
Kevin Rave
Kevin Rave

Reputation: 14426

CSV in Where Condition Mysql

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?

Upvotes: 1

Views: 308

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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';

Upvotes: 2

Mike Brant
Mike Brant

Reputation: 71384

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

Upvotes: 1

Related Questions