Urgent Query with Query Prob!

[asmithy]

Hi people,
I'm new to ColdFusion and I'm already stuck with a query problem!
Basically I have 2 tables, Table 1 contains fields name, address, email address.
Table 2 has the same fields.
I want to query all the records in Table 2 in one go with the values from
Table 1.
Then I want to output all the records in Table 2 which match the values
from each of the records in Table 1.
Can anyone help please.
Below is what I have at the moment but it only compares record 1 from
Table 1 with the records in Table2, it doesn't go on and compare the
other records in Table1 with the records in Table 2 according
to the CFOUTPUT display.

<cfquery name=&quot;Query1&quot; datasource=&quot;#Search#&quot;>
SELECT *
FROM UserDetails
</cfquery>

<CFQUERY NAME=&quot;Query2&quot; DATASOURCE=&quot;#Search#&quot;>
SELECT name,address,email
FROM SearchDetails, UserDetails
WHERE
<CFIF #Query1.name# NEQ &quot;&quot;>
SearchDetails.name = '#Query1.name#'
</CFIF>
<CFIF #Query1.address# NEQ &quot;&quot;>
AND SearchDetails.address = '#Query1.address#'
</CFIF>
<CFIF #Query1.email# NEQ &quot;&quot;>
AND SearchDetails.email = '#Query1.email#'
</CFIF>
</CFQUERY>

<CFOUTPUT QUERY=&quot;Query2&quot;>
#name#<br>
#address#<br>
#email#<br>
</CFOUTPUT>

 

[sriram]

You need to loop query1 to get your results.

I am slightly modifying it. Try it.


<cfquery name=&quot;Query1&quot; datasource=&quot;#Search#&quot;>
SELECT *
FROM UserDetails
</cfquery>

<!--- loop starts here ---->
<CFLOOP QUERY=&quot;Query1&quot;>

<CFQUERY NAME=&quot;Query2&quot; DATASOURCE=&quot;#Search#&quot;>
SELECT name,address,email
FROM SearchDetails, UserDetails
WHERE
<CFIF #Query1.name# NEQ &quot;&quot;>
SearchDetails.name = '#Query1.name#'
</CFIF>
<CFIF #Query1.address# NEQ &quot;&quot;>
AND SearchDetails.address = '#Query1.address#'
</CFIF>
<CFIF #Query1.email# NEQ &quot;&quot;>
AND SearchDetails.email = '#Query1.email#'
</CFIF>
</CFQUERY>

<CFOUTPUT QUERY=&quot;Query2&quot;>
#name#<br>
#address#<br>
#email#<br>
</CFOUTPUT>

</CFLOOP>
<!--- loop ends here --->