I am having two table

Table 1 having a field

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

CodePudding user response：

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.

CodePudding user response：

In this case your "Table2" is not in normalized.

In a relational database, you are supposed to structure your table in order to have a single and atomic value for each column of each row. So, rather than having a list of multiple book_ids you need to have more rows each one containing a single book_id.

The query using the IN statement will then succeed.

In this case you are storing a list inside a column, but you are then now allowed to check if a value is contained by a certain set.

CodePudding user response：

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