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:266683
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
