you are never really defeating the plan.
"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Teddy Payne To: CF-Talk Sent: Tue Jan 16 21:49:57 2007 Subject: Re: SQL Join & Data group output Richard, I did not like the TSQL dynamic SQl that I created. I always hate defeating an execution plan. Here is a CF way: <!--- I used ad hoc queries for the sake of time. I highly recommend putting this in a multi-result stored procedure ---> <cfquery datasource="test" name="qryEntry"> select entryID , entryCol1 from dbo.tblEntry </cfquery> <cfquery datasource="test" name="qryEntryService"> select e.entryID , s.serviceCol1 from dbo.tblEntry e join dbo.tblEntryService es on e.entryID = es.entryID join tblService s on es.serviceID = s.serviceID </cfquery> <cfquery datasource="test" name="qryService"> select serviceID , serviceCol1 from dbo.tblService </cfquery> <cfset arData = ArrayNew(1)> <!--- Create the empty service columns in your array ---> <cfloop query="qryEntry"> <cfset arData[qryEntry.currentrow] = StructNew()> <cfset arData[qryEntry.currentrow].entryID = qryEntry.entryID> <cfset arData[qryEntry.currentrow].entryCol1 = qryEntry.entryCol1> <cfloop query="qryService"> <cfset arData[qryEntry.currentrow]["#qryService.serviceCol1#"] = " "> </cfloop> </cfloop> <cfset found = 0> <cfloop query="qryEntryService"> <!--- Find where entry is located in the array ---> <cfloop from="1" to="#ArrayLen(arData)#" index="i"> <cfif arData[i].entryID eq qryEntryService.entryID> <cfset found = i> </cfif> </cfloop> <!--- Update the X in the column that represents the column ---> <cfset arData[found]["#qryEntryService.serviceCol1#"] = "X"> <cfset found = 0> </cfloop> <cfdump var="#arData#"> My schema was: CREATE TABLE [tblEntry] ( [entryID] [int] IDENTITY (1, 1) NOT NULL , [entryCol1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_tblEntry] PRIMARY KEY CLUSTERED ( [entryID] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [tblEntryService] ( [entryServiceID] [int] IDENTITY (1, 1) NOT NULL , [entryID] [int] NULL , [serviceID] [int] NULL , CONSTRAINT [PK_tblEntryService] PRIMARY KEY CLUSTERED ( [entryServiceID] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [tblService] ( [serviceID] [int] IDENTITY (1, 1) NOT NULL , [serviceCol1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_tblService] PRIMARY KEY CLUSTERED ( [serviceID] ) ON [PRIMARY] ) ON [PRIMARY] GO On 1/16/07, Richard Cooper <[EMAIL PROTECTED]> wrote: > > That'd be great. I'm using SQL Server 2000. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266711 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
