In query in SQLite

"IN" query is not working. Please guide me if i am wrong.

KaizenResultsInformationTable is MasterTable having fie开发者_运维问答ld "recordinfo", this field contains Child table Ids as string. kaizenResultsRecordInformationTable is Childtable having field "recordId".

I have to match records of child.

Query:

select recordinfo from KaizenResultsInformationTable

Output: ;0;1;2;3;4;5;6;7;8;9;10

Query:

select substr(replace(recordinfo,';','","'),3,length(recordinfo))   
from KaizenResultsInformationTable`

Output: "0","1","2","3","4","5"

This query is not working:

select * from kaizenResultsRecordInformationTable 
where substr(recordid,0,2) in (
    select substr(replace(recordinfo,';','","'),3,length(recordinfo))
    from KaizenResultsInformationTable
)

This query is working:

select * from kaizenResultsRecordInformationTable 
where substr(recordid,0,2) in ("0","1","2","3","4","5")

You can't use in like that. In your second query, you are passing in a single string containing a comma-separated list of values.

It is better to represent a list of IDs as one record for each value.

Also, I'm not sure why you are taking a substring of your recordid. You should usually be storing one value per column.

However, if you can't change the schema, you can use string matching with 'like' instead of 'in'. Something like this should work:

select a.* from kaizenResultsRecordInformationTable a
join KaizenResultsInformationTable b 
  on (';'+b.recordinfo+';') LIKE ('%;'+trim(substr(recordid,0,2))+';%')

So if your recordinfo looks like 1;2;3;4;5;6, and your substr(recordid,0,2) looks like 1, this will include that row if ";1;2;3;4;5;6;" LIKE "%;1;%", which is true.