Problem with IN sub clause

[P J]

I have a query along the lines of

 

ExecuteSQL ( "SELECT IDa, Name, Age FROM tableA WHERE  IDa IN ( SELECT IDb FROM tableB WHERE IDc = '1234' )" ; "XX"; "YY" )

 

I have removed most of the fields to be returned as well as additional WHERE criteria that is working.

 

I need the XX and YY in the outside query, but I need commas as the record separator for the IN ( SELECT... sub query

 

Am I missing something obvious....

[jlamprecht]

I would take that IN clause out and run it as its own ExecuteSQL and put the result into a variable. Format it correctly in the fashion IN ('1','2','3'). Then put the variable into your ExecuteSQL you have provided.

[P J]

Sorry, I clearly didn't provide enough information.  

 

I realise that your suggestion is technically a good solution.  In my circumstances I have a single ExecuteSQL which is dynamically generated.  A variable contains either a basic WHERE structure and or the IN sub query.  To take your suggestion would require fundamental re structuring of what is a parameter driven generic process which I'd rather avoid.

 

I tried nesting ExecuteSQL, but was unable to get it to work.  I was hoping that there was something clever in SQL itself that would help me (not being an expert myself)

 

Actual query look more like this

 

ExecuteSQL ( "SELECT " & $fieldlist & " FROM " & $tablename & " WHERE  " & $whereQuery AND active = 1 " ;  "XX"; "YY" )

 

Where $whereQuery is either

 

"IN ( SELECT " & $inField & " FROM " & $inTable &" WHERE " & $qField & " = '" & $qData & "' )" 

 

Or more usually

 

"$qField & " = '" & $qData & "'" 

 

Thanks for your input.  In responding I have thought of a couple of ideas to try...