|Subject||Problem in executing query using 'IN' statement.|
I have a query in which I check whether an NAME _IDS of type varchar is in another table with NAME _ID column having data type as int. Also, ID_1 have ID’s separated by comma’s example ‘1,2,3,4,5’.
Below is the query which I am trying to execute
SELECT Tb2. _ID,Tb1.DET_ID,COUNT(Tb3. _Name) NAME_COUNT
FROM TABLE_1 Tb1
JOIN TABLE_2 Tb2 ON Tb2.DET_ID= Tb1.DET_ID
JOIN Table_3 Tb3 ON Tb3. _ID= Tb1. _ID
AND CAST(Tb3.Time AS DATE)=CURRENT_DATE
AND Tb3.NAME _IDS IN (CAST(Tb2.NAME_ID AS VARCHAR(250))) /*Here Tb3.NAME _IDS is varchar & Tb2.NAME _IDS is int so casted into varchar. This line gives wrong output */
GROUP BY Tb1.DET_ID, Tb1. NAME _ID, Tb1. _ID, Tb3._NAME
NOTE: Tb3.NAME _IDS could have multiple ids in form ‘1,2,23,15’
Problem I am facing is that when Tb3.NAME _IDS contains multiple IDs in it then result is not shown leading to wrong result. Is there any solution to solve this query. I tried using ‘STARTING WITH’, ‘CONTAINING’,’LIKE’ but no output.