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="Query1" datasource="#Search#">
SELECT *
FROM UserDetails
</cfquery>
<CFQUERY NAME="Query2" DATASOURCE="#Search#">
SELECT name,address,email
FROM SearchDetails, UserDetails
WHERE
<CFIF #Query1.name# NEQ "">
SearchDetails.name = '#Query1.name#'
</CFIF>
<CFIF #Query1.address# NEQ "">
AND SearchDetails.address = '#Query1.address#'
</CFIF>
<CFIF #Query1.email# NEQ "">
AND SearchDetails.email = '#Query1.email#'
</CFIF>
</CFQUERY>
<CFOUTPUT QUERY="Query2">
#name#<br>
#address#<br>
#email#<br>
</CFOUTPUT>
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="Query1" datasource="#Search#">
SELECT *
FROM UserDetails
</cfquery>
<CFQUERY NAME="Query2" DATASOURCE="#Search#">
SELECT name,address,email
FROM SearchDetails, UserDetails
WHERE
<CFIF #Query1.name# NEQ "">
SearchDetails.name = '#Query1.name#'
</CFIF>
<CFIF #Query1.address# NEQ "">
AND SearchDetails.address = '#Query1.address#'
</CFIF>
<CFIF #Query1.email# NEQ "">
AND SearchDetails.email = '#Query1.email#'
</CFIF>
</CFQUERY>
<CFOUTPUT QUERY="Query2">
#name#<br>
#address#<br>
#email#<br>
</CFOUTPUT>