David,
Even if you get multiple records from your first query, you are only using the first one in your second query. What you need to do is convert the resultset of the first query to a list, then use the second query to find all those IDs that are IN the resultset list. Loyd Campbell David Allison <[EMAIL PROTECTED]>@dfwcfug.org on 10/18/2001 12:28:13 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: [EMAIL PROTECTED] cc: Subject: help with SQL statement I am having trouble with a sql statement. I have a form where a user selects a state and Category and the output is people in the db who match both. Everything I have tried I only get one result.Here is my latest attempt: <cfquery name=getID datasource="#application.DSN#"> SELECT e.CategoryID, e.PhotographerID, p.CompanyName FROM tbPhotographers p INNER JOIN tbevent e ON p.PhotographerID = e.PhotographerID WHERE e.CategoryID = #FORM.Category# </cfquery> <CFQUERY NAME="searchresults" DATASOURCE="#application.DSN#"> SELECT i.PhotographerID, i.WorkPhone, a.City, a.State, a.ZipCode FROM tbPhotographerAddress a INNER JOIN tbContactInfo i ON a.PhotographerID = i.PhotographerID WHERE i.PhotographerID = #getID.PhotographerID# AND a.State = "#form.State#" </CFQUERY> This works but it only gives me one result and I know there should be more. I am a beginner and have been working on this to long so any help would be appreciated! Thanks, DA __________________________________________________ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org
