Hi thanks for that I have changed the query to the following:
Select tbl_advertisers.advertiserid, advertiser, logo_thumb I got duplicate records even with just the advertiserid and the advertiser (name). It is midnight here so not thinking real clearly. Cant see why I am getting duplicate records. P -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Sunday, 27 May 2007 11:01 PM To: CF-Talk Subject: RE: Desperate help with a query Instead of selecting every column, you need to select only the columns you need. Your SELECT DISTINCT * is bringing back every combination of every column's value in the five tables. In other words, it's bringing back every record not filtered by your WHERE clause. Try fixing that problem, first, by selecting only one column. Then, add a single column, as you go, and make note of when you start getting duplicate records. At that point, you probably have selected too many columns for your need. BTW, do you have any pictures of their satisfied clients? ;^) M!ke -----Original Message----- From: Pete [mailto:[EMAIL PROTECTED] Sent: Sunday, May 27, 2007 7:58 AM To: CF-Talk Subject: Desperate help with a query Hi there I have got a website up and running and just realized it is throwing wrong results and I cant see why this is happening. Basically the website can be viewed at: http://www.cosmeticbeautydirectory.com.au <http://www.cosmeticbeautydirectory.com.au/> So where is the problem - its in the advanced search: Select the option breast augmentation from the drop down list, leave the second selection as is and press submit. The results displayed - several are in duplicate. Its obviously got something to do with the query: <cfquery name="qGetAdvertisers" datasource="#request.db_dsn#" username="#request.db_login#" password="#request.db_pwd#"> Select distinct * From tbl_advertisers, tbl_procedures, tbl_states, tbl_advertiserstates, tbl_advertiserprocs Where ( tbl_advertisers.advertiserid = tbl_advertiserprocs.advertiserid And tbl_procedures.procedureid = tbl_advertiserprocs.procedureid And tbl_advertisers.advertiserid = tbl_advertiserstates.advertiserid And tbl_states.stateid = tbl_advertiserstates.stateid <!--- Search by procedureid ---> <cfif FORM.ProcedureID IS NOT ""> AND tbl_procedures.ProcedureID = '#Form.ProcedureID#' </cfif> <!--- Search by stateid ---> <cfif FORM.stateid IS NOT ""> AND tbl_states.stateid = '#FORM.stateid#' </cfif> ) </cfquery> Just wondering if anyone might be able to shed some light on the query. I need to fix it as soon as I possibly can. Thanks in advance. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279363 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
