Thanks for the advice, Jim! Rick
-----Original Message----- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Monday, July 31, 2006 12:46 PM To: CF-Talk Subject: Re: Why would this query do this? > > Do I need a join for this? > You are actually doing a join in your query, it just isn't using ANSI join syntax. Your query could be re-written as... Select C.Client_ID,C.Client_First_Name,C.Client_Middle_Name,C.Client_Last_Name, C.Client_Name_Suffix FROM clients C INNER JOIN accounts A ON C.Client_ID = A.Client_ID WHERE A.Company_ID = '#Get_Insurance_Companies.Company_ID#' ORDER BY C.Client_Last_Name (removing the INSURANCE_COMPANIES from the join, as Aaron pointed out, that is unnecessary) .....which would be in the "standard" format, and would be more portable across databases. You also might look at doing a query that returns all insurance companies along with their accounts and clients (using a LEFT or RIGHT join to make sure you get any insurance companies that don't have accounts or clients...if that is what you want). Order the query by insurance company, account, client, and then use the GROUP attribute of cfoutput to output the data. That would eliminate the cfloop and multiple trips to the db. -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248282 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
