Den 16.09.2016 15:34, skrev 'Joje' 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
  • [firebird-support]... 'Joje' j...@codework-solutions.com [firebird-support]
    • Re: [firebird... Virna Constantin costel...@yahoo.com [firebird-support]
      • [firebird... fabia...@itbizolutions.com.au [firebird-support]
    • Re: [firebird... setysvar setys...@gmail.com [firebird-support]
      • RE: [fire... 'Joje' j...@codework-solutions.com [firebird-support]
        • Re: [... setysvar setys...@gmail.com [firebird-support]
          • R... j...@codework-solutions.com [firebird-support]

Reply via email to