I have a query where I am joining two tables and would like the output to be in
3 columns. I am familiar with doing it, but cannot seem to with the data in
question. The query returns 27 parent categories and around 200 sub (child)
categories that are related. I tried using mod 3 but with the number of results
from the join I cannot get it. I have tried grouping by different columns with
no luck
What I would like to see is 3 columns like below.
[CATEGORYNAME]
subCategoryName,subCategoryName,subCategoryName,
subCategoryName,subCategoryName,subCategoryName,
subCategoryName,subCategoryName,subCategoryName
The code I have so far that is not working.
The query
<CFQUERY datasource="#application.DB#" name="AllMainSubCatsRet"
username="#application.un#" password="#application.pw#">
SELECT
sc.scategoryID,
sc.categoryID,
sc.subCategoryName,
c.categoryName,
c.catstatus,
c.categoryImage
FROM dbo.CLSFD_CategoriesSub_1 sc
INNER JOIN dbo.CLSFD_Categories c ON sc.categoryID = c.categoryID
WHERE c.catstatus = <CFQUERYPARAM cfsqltype="cf_sql_bit"
value="#ARGUMENTS.catStatus#">
ORDER BY c.categoryID ASC
</CFQUERY>
The page code
<!---GET ALL CATEGORIES AND THEIR CORRESPONDING SUB-CATEGORIES--->
<CFINVOKE
component="totallyclassified.components.commonQrys"
method="InitAllMainSubCats"
returnvariable="AllMainSubCatsRet">
<CFINVOKEARGUMENT name="catStatus" value="1"/>
</CFINVOKE>
<table border="0" cellpadding="2" cellpsacing="2" width="980"
style="border: 1px solid ##ccccee;">
<CFOUTPUT query = "AllMainSubCatsRet" group="categoryID">
<!--- Determine if this is record 1, 4, 7, etc...--->
<!--- If so, open the row. --->
<CFIF currentrow mod 3 is 1>
<tr>
</CFIF>
<td width="33%"><!---OUTPUT SOME STUFF---></td>
<!--- Determine if this is record 3, 6, 9, etc... --->
<!--- If so, close the row. --->
<CFIF currentrow mod 3 IS 0>
</TR>
</CFIF>
</CFOUTPUT>
<!--- If the query record count is not equally divisible by 3, --->
<!--- the last row was not close. --->
<!--- Determine how many more columns are needed, create them --->
<!--- and close the row. --->
<CFIF AllMainSubCatsRet.recordCount mod 3 is not 0>
<CFSET ColsLeft = 3 - (AllMainSubCatsRet.recordCount MOD 3)>
<CFLOOP index = "i" from="1" to="#ColsLeft#">
<td> </td>
</CFLOOP>
</TR>
</CFIF>
</table>
</CENTER>
<!--- END TEMPLATE --->
Doug B.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:265922
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
