There are probably many ways to do this, but one thing you might find
useful is to make use of a CROSS JOIN in your query...that way you can
return one record for each combination of tbl1ID and service, and then
use a LEFT JOIN to determine if there is a match in Table 2...something
like...
SELECT table_1.tb1ID,table_1.field1,table_1.field2,table_1.field3,
CASE WHEN table_2.tbl3ID IS NULL THEN 0 ELSE 1 END AS servyesno
,table_3.service
FROM tbl3 as table_3 CROSS JOIN tbl1 as table_1 LEFT JOIN
tbl2 as table_2 ON table_3.tbl3ID = table_2.tbl3ID AND table_2.tbl1ID =
table_1.tb1ID
ORDER BY tb1ID,service
Then, when you output your records, you know you have a record for each
spot in the grid....
<table>
<thead>
<tr>
<th>tb1ID</th>
<th>field1</th>
<th>field2</th>
<!---one way of just getting one set of labels...could perhaps be more
efficient--->
<cfset breakloop = example.tb1ID>
<cfoutput>
<cfloop query="example">
<th>#service#</th>
<cfif tb1ID IS NOT breakloop><cfbreak></cfif>
</cfloop>
</cfoutput>
<th>field3</th>
</tr>
</thead>
<cfoutput query="example" group="tb1ID">
<tr>
<td>#tbl1#</td>
<td>#field1#</td>
<td>#field2#</td>
<cfoutput>
<td><cfif servyesno>x</cfif></td>
</cfoutput>
<td>#tbl1#</td>
</tr>
</cfoutput>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:266713
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
