WHERE using results from another querie

[Manuel_Lino]

Hi,

Example:
<cfquery name="QUERY 1" datasource="MyDatabase">
SELECT Table1ID, A, B, C
FROM Table1
WHERE A = #Form.A#
<cfoutput query="QUERY1">#A#<br></cfoutput>
It returns:
3
4

<cfquery name="QUERY 2" datasource="MyDatabase">
SELECT Table2ID, A, Z, Y
FROM Table2
WHERE A IN (3,4)
<cfoutput query="QUERY 2">#Table2ID#</br></cfoutput>
It returns:
a
b
c
d
e
f

But if I make QUERY2 as:
<cfquery name="QUERY 2" datasource="MyDatabase">
SELECT Table2ID, A, Z, Y
FROM Table2
WHERE A IN (#QUERY1.A#)
it returns only:
a
b
c

The values a,b,c are the Table2ID's where A=3
The values d,e,f are the Table2ID'a where A=4

How can I make return all values?

Hope you understand.
Thanks
Manel

[paross1]

(#QUERY1.A#) only contains the A column value from one row returned, since you aren't looping through the query to get all rows. In other words, it isn't a list. You may want to loop the the first query using cfloop or cfoutput and a query attribute to populate a list that you can then use in your second query.

Phil

[Manuel_Lino]

Thanks for your reply.

I´ve tried this but it didn´t work. Please help.

<cfquery name="QUERY 1" datasource="MyDatabase">
SELECT Table1ID, A, B, C
FROM Table1
WHERE A = #Form.A#
</cfquery>

<cfloop query="QUERY1">
<cfquery name="QUERY 2" datasource="MyDatabase">
SELECT Table2ID, A, Z, Y
FROM Table2
WHERE A IN (#QUERY1.A#)
</cfquery>
</cfloop>

Could you explain what i´m missing.
It returns an error saying : Incorrect sytax near '3'

Thanks
Manuel

[paross1]

Think about what you are doing for a second. You are selecting multiple rows of data with your first query. Then you are using a cfloop to loop through each row returned from that first query to perform another query that uses the value returned in the A column. The problem is that you are executing your second query once for each row returned by your first query, and you really aren't saving that result anywhere, since each iteration of the loop causes your cfselect to overwrite your previous results. You can create a list variable and populate it with the results from your first query, but I have to ask, unless you need the results from your first query for something else, why don't you just join the two tables and use on equery?

Based on what you have, something like this?

<cfquery name="QUERY 2" datasource="MyDatabase">
SELECT T2.ID, T2.A, T2.Z, T2.Y
FROM Table2 T2
INNER JOIN Table1 T1 ON T2.A = T1.A
WHERE T1.A = #Form.A#
</cfquery>

Phil

[Manuel_Lino]

Yes, I do need it to return the results. The inner join is actually already done. How should I make the second query to return the values like in the example where I make the WHERE clause "WHERE A IN (3,4)". When I do this, I get the report I need grouping by 3 and 4.
Could you explain how to get the list variable and use it in the second query? I'll appreciate.

Thanks again Phil
Manuel

[Manuel_Lino]

Thanks Phil.
Created:
<cfset temp = ValueList(QUERY1.Table1ID)>
and set the WHERE clause:
WHERE Table2ID IN (#temp#)
It worked beautifully

Thanks again :)

Manuel

[paross1 · Accepted solution]

I'm guessing something like this:

<cfquery name="QUERY1" datasource="MyDatabase">
SELECT Table1ID, A, B, C
FROM Table1
WHERE A = #Form.A#
</cfquery>

<CFSET your_list = ValueList(QUERY1.A)>

<cfquery name="QUERY2" datasource="MyDatabase">
SELECT Table2ID, A, Z, Y
FROM Table2
WHERE A IN (#your_list#)
</cfquery>

(You would use quotedvaluelist() and preservsinglequotes() if you wanted a list of non-numeric values.)

Phil