The reason I have 2 Tables is because this part of my application will do
the follwing:
Customers fill out a form to specify their search criteria, these details
are stored into Table 1 whcih will have the fields, Cust_name,Cust_address,
Cust_email,Car_model_type,
Yr_of_model,Price_range.
Using the values in Table1 a SCHEDULED event will query Table2 to match the
records in Table 1 with records in Table 2 that contain details of car
owners who want to sell their vehicles.
Any matching records found in Table 2 that meet each of the records in
Table1 are e-mailed in a batch using the email field in Table 1 to the
customers.
The output test is used to check if the query2 is working.
So, theoretically if record 1 in Table1 has the value
Car_model_type = Ford focus and 5 records exists or 5 Car owners exist in
Table2 that have the value "Ford focus" in the Car_model field then, these 5
records should be emailed to the customer in record1 / table 1.
I think a loop might have to be used to batch process the results to all the
customers in Table 1 at one go???
------Original Message------
From: "Graham Lewis" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: August 30, 2000 2:50:54 PM GMT
Subject: Re: Query with a Query problem!
Just a thought: I don't know the details of your database
application but from your description, I wonder whether you need to
have two tables. If the same fields are in both, perhaps you could
manage with one table and perhaps an additional field that makes
whatever distinction having them in two tables currently makes.... if
you see what I mean.
Graham
On 30 Aug 00, at 10:06, Adam Smith wrote:
> 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>
>
>
> -----------------------------------------------
> FREE! The World's Best Email Address @email.com
> Reserve your name now at http://www.email.com
>
>
>
------------------------------------------------------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or s
end a message to [EMAIL PROTECTED] with 'unsubscribe' in the
body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
-----------------------------------------------
FREE! The World's Best Email Address @email.com
Reserve your name now at http://www.email.com
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
