RE: Query with a Query problem!
by default, if you don't specify a QUERY attribute in the CFOUTPUT, and just scope the variable with the query name, you'll get the first record. if you wrap the second CFQUERY in a CFOUTPUT, you'll be good. eg. CFOUTPUT QUERY="Query1" CFQUERY NAME="Query2" DATASOURCE="#Search#" SELECT name,address,email FROMSearchDetails, 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 CFIF Query2.recordcount NEQ 0 #name#br #address#br #email#br /CFIF /CFOUTPUT Chris Olive, DOEHRS Website Administrator -Original Message- From: Adam Smith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 30, 2000 10:06 AM To: [EMAIL PROTECTED] Subject: Query with a Query problem! 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 FROMSearchDetails, 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=listsbody=lists/cf_talk or send 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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Query with a Query problem!
Try this... cfquery name="Query1" datasource="#Search#" SELECT * FROM SearchDetails, UserDetails WHERE SearchDetails.name = UserDetails.name AND SearchDetails.adress = UserDetails.address AND SearchDetails.email = UserDetails.email /cfquery -Original Message- From: Adam Smith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 30, 2000 10:06 AM To: [EMAIL PROTECTED] Subject: Query with a Query problem! 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 FROMSearchDetails, 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=listsbody=lists/cf _talk or send 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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Query with a Query problem!
Adam, If you want only the records that match from table 1 and table 2, try this: CFQUERY NAME="yourquery" DATASOURCE="yourDSN" SELECT * FROMSearchDetails, UserDetails WHERE SearchDetails.name = UserDetails.name AND SearchDetails.address = UserDetails.address AND SearchDetails.email = UserDetails.email /cfquery Original Message Follows From: Adam Smith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Query with a Query problem! Date: Wed, 30 Aug 2000 10:06:04 -0400 (EDT) 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 FROMSearchDetails, 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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
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 FROMSearchDetails, 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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Query with a Query problem!
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 FROMSearchDetails, 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=listsbody=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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Query with a Query problem!
Chris, I tried what you suggested but I still got 1 record displayed when there where actually 3 records in Table 2 that matched the record values in Table 1 e.g CFIF Query2.recordcount NEQ 0 !--- Display the number of records matched --- pThe SEARCH RESULT count is: #query2.RecordCount#. p !--- Show details of the records mathced --- #name#br #address#br #email#br /CFIF /CFOUTPUT Hence even though the screen display the following The SEARCH RESULT count is: 3. It only displayed the full details of 1 record??? --Original Message-- From: "Olive, Christopher M Mr NMR" [EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'" [EMAIL PROTECTED] Sent: August 30, 2000 2:20:38 PM GMT Subject: RE: Query with a Query problem! by default, if you don't specify a QUERY attribute in the CFOUTPUT, and just scope the variable with the query name, you'll get the first record. if you wrap the second CFQUERY in a CFOUTPUT, you'll be good. eg. CFOUTPUT QUERY="Query1" CFQUERY NAME="Query2" DATASOURCE="#Search#" SELECT name,address,email FROMSearchDetails, 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 CFIF Query2.recordcount NEQ 0 #name#br #address#br #email#br /CFIF /CFOUTPUT Chris Olive, DOEHRS Website Administrator -Original Message- From: Adam Smith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 30, 2000 10:06 AM To: [EMAIL PROTECTED] Subject: Query with a Query problem! 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 FROMSearchDetails, 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=listsbody=lists/cf_talk or send 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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Query with a Query problem!
I think what you want is something like so: cfoutput query="yourquery" group="thegroupthatthethreerelateto" cfoutput #name#br #address#br #email#br /cfoutput /cfoutput See if that works. -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
