Will the number of services be dynamic? Teddy
On 1/16/07, Richard Cooper <[EMAIL PROTECTED]> wrote: > > Hi all, > > This explanation is a bit long but it kind of needed it to explain what I > was trying to achieve. > > I have three tables which I would like to join for use within creating a > table of results > > Table 1: has several entries > Table 3: has a list of services > Table 2: join these two table using the primary keys > > Table: 1 > | tb1ID | field1 | field2 | field3 | > -------------------------------------- > | 1 | blah | asdf | asdf | > | 2 | 1234 | asdf | asdf | > | 3 | 5555 | asdf | asdf | > | 4 | 6666 | asdf | asdf | > > Table: 2 > | tbl2ID | tbl1ID | tbl3ID | > ---------------------------- > | 1 | 1 | 1 | > | 2 | 1 | 2 | > | 3 | 1 | 3 | > | 4 | 2 | 2 | > | 5 | 2 | 4 | > | 6 | 3 | 1 | > | 7 | 4 | 2 | > | 8 | 4 | 3 | > > Table: 3 > | tbl3ID | service | > -------------------- > | 1 | serv1 | > | 2 | serv2 | > | 3 | serv3 | > | 4 | serv4 | > > > I would like it to spit out the results like this: > > Output: > | tb1ID | field1 | field2 | serv1 | serv2 | serv3 | serv4 | field3 | > ----------------------------------------------------------------------- > | 1 | blah | asdf | x | x | x | | asdf | > | 2 | 1234 | asdf | | x | | x | asdf | > | 3 | 5555 | asdf | x | | | | asdf | > | 4 | 6666 | asdf | | x | x | | asdf | > > > > The example code I was thinking should be along the lines of: > > <cfquery name="example" datasource="DSN"> > SELECT table_1.tb1ID > ,table_1.field1 > ,table_1.field2 > ,table_1.field3 > ,table_3.tbl3ID > ,table_3.service > FROM tbl3 as table_3 > LEFT OUTER JOIN > tbl2 as table_2 > ON table_3.tbl3ID = table_2.tbl3ID > LEFT OUTER JOIN > tbl1 as table_1 > ON table_2.tbl1ID = table_1.tbl1ID > </cfquery> > > <table> > <thead> > <tr> > <th>tb1ID</th> > <th>field1</th> > <th>field2</th> > <cfoutput query="example" group="tbl3ID"> > <th>#service#</th> > </cfoutput> > <th>field3</th> > </tr> > </thead> > <cfoutput query="example" group="tb1ID"> > <tr> > <td>#tbl1#</td> > <td>#field1#</td> > <td>#field2#</td> > <cfoutput> > <td><cfif service NEQ ''>x</cfif></td> > </cfoutput> > <td>#tbl1#</td> > </tr> > </cfoutput> > </table> > > > The problem is the services are being repeated in the table head and are > therefore not unique. Ideally I'd like it such that they always occur and > could even be ordered etc. > > I really can't get my head round this so help is much needed. > Thanks, > > R > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:266688 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
