Hi Setysvar,
Thank you for helping out. But I am unable to run it on my "SQL manager" on executing query I am getting 'Invalid Token' error. Below is error that is shown Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 5, column 61. ','. Also, could you tell me how does ','||ColName ||',' syntax works. I am seeing it for the first time. Thank you. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 16 September 2016 22:23 To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Problem in executing query using 'IN' statement. Den 16.09.2016 15:34, skrev 'Joje' j...@codework-solutions.com <mailto:j...@codework-solutions.com> [firebird-support]: 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. SELECT Tb2._ID, Tb1.DET_ID, COUNT(Tb3._Name) NAME_COUNT /* It always confuses me when I see things like COUNT(Tb3._Name), myself I always use either COUNT(*) or COUNT(DISTINCT Tb3._Name), though I think your syntax is correct if you want to do the equivalent of COUNT(*), but skip nulls */ 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 ','||Tb2.NAME_ID||',' in ','||Tb3.NAME_IDS||',' /* Tb2 and Tb3 the other way around, and add commas to make sure things matches exactly (i.e. match even if the first or last in NAME_IDS and not match 1 to 11 etc) */ GROUP BY Tb2._ID, Tb1.DET_ID /*It makes no sense to group by anything but the non-grouped fields that you select, most likely you would get an error with your original GROUP BY*/ HTH, Set