Database select on values in another CFQUERY

[TheVMan]

I have two queries, one dependent on on the other. The first query is as follows:
<cfquery name=&quot;Query1&quot;>
SELECT A,
B
FROM TABLE
WHERE A = '#form.field#'
</cfquery>

This query obviously queries on a value passed from another form. Multiple rows can be returned based on that value. An example of these results would be:

A B
16 Works1
16 Works2
16 Works3

The second query is as follows:
<cfquery named=&quot;Query2&quot;>
SELECT C,
D
FROM TABLE2
WHERE D IN '#Query1.B#'
</cfquery>

The problem here is I don't know how to get Query 2 to use the multiple values from Query1. Since Column B from Query1 returned values of Works1, Works2, and Works3, I'm not sure how to programmatically perform that query with those values. Any help would be appreciated.

V

 

[faust13]

Solution:

In your query tag, throw the attribute maxrows=&quot;1&quot;. It'll return your first record from the query only. ----------------------------------------
Is George Lucas Kidding...

 

[tleish]

You could do it this way:

<cfquery name=&quot;Query1&quot;>
SELECT A, B
FROM TABLE
WHERE A = '#form.field#'
</cfquery>

<CFSET aQuery1_B = ArrayNew(1)>
[COLOR=666666]<!--- I use CFLOOP instead of CFOUTPUT to cut down on the &quot;white space&quot; output --->[/color]
<CFLOOP QUERY=&quot;Query1&quot;>
<CFSET ArrayAppend(aQuery1_B, &quot;'&quot; & Query1.B & &quot;'&quot;)>
</CFLOOP>

<cfquery named=&quot;Query2&quot;>
SELECT C, D
FROM TABLE2
WHERE D IN (#ArrayToList(aQuery1_B)#)
</cfquery> - tleish