mysql select statement IN

Question

I am having two table

Table 1 having a field

id	book_ids
1	1,2,3

Table 2 have all the book Ids

select * 
from table 2 
where book_id in (select book_ids from table 1 where id=1) ;

this statement not returning all the book ids from table 2 having id 1,2,3

Can anyone help

Before doing anything else please read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — RiggsFolly, Jun 24 '22 at 13:07
Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Alon Eitan, Jun 24 '22 at 13:12
may be it is not the right way to store data, Still is there any solution available — user2902377, Jun 24 '22 at 13:15

score 0 · Answer 1 · answered Jun 24 '22 at 13:17

0

try to store table1 values in rows of table not in a same field. and then your SELECT IN works.

answered Jun 24 '22 at 13:17

Abhay

66
6

score 0 · Accepted Answer · answered Jun 24 '22 at 13:28

You could use the FIND_IN_SET() function:

select * 
from table 2 
where FIND_IN_SET(book_id, (select book_ids from table 1 where id=1)) > 0;

Read the documentation I linked to for details on how that function works.

But only do this if your table remains small. Using this function spoils any opportunity to optimize the query with an index, so the larger your table gets, the performance will grow worse and worse.

Also FIND_IN_SET() doesn't work the way you expect if there are spaces in your comma-separated list.

SELECT * FROM table2 WHERE FIND_IN_SET(book_id, (select book_ids from table1 where id = 1)) ; This is working all fine Thanks a lot Bill Karwin — user2902377, Jun 24 '22 at 13:46

mysql select statement IN

2 Answers2