Ok, I did an outer join and it fixed my problem of not being ale to display
categories not having any sub categories.
Thanks.
SELECT mnu_categories.category_id,
mnu_categories.category_name,
mnu_categories.category_url,
mnu_sub_categories.sub_cat_id,
mnu_sub_categories.sub_cat_name
FROM mnu_categories, mnu_sub_categories
WHERE mnu_categories.category_id = mnu_sub_categories.category_id (+)
{outer join syntax for Oracle}
ORDER BY
mnu_categories.category_name,
mnu_sub_categories.sub_cat_nam
----- Original Message -----
From: "Courtney Payne" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 06, 2000 12:49 PM
Subject: RE: MULTIPLE COLUMN output problem!
> Ok, try this. Didn't have the tables set up to test this against but it
> should point you in the right direction.
>
>
> <!--- Get number of top-level categories for TD delimitting later --->
> <cfquery name="GetNumCategories" ...>
> SELECT DISTINCT(CategoryName)
> FROM [your table]
> </cfquery>
>
> <cfset numcategories = GetNumCategories.recordcount>
>
> <!--- Your query here --->
> <cfquery name="GetAllCategories" ...>
> .
> .
> .
> </cfquery>
>
> <!--- Since you can't use currentrow (because you're grouping), maintain
> your own category (top-level) counter --->
> <cfset count = 0>
> <!--- You're only gonna have 2 TDs in your table - one on the left, one on
> the right. --->
> <table>
> <tr>
> <td valign="top">
> <cfoutput query="GetAllCategories"
> group="CategoryName">
>
> <cfset count = count + 1>
> <!--- TD delimit check --->
> <cfif count EQ ceiling(numcategories / 2)>
> </td><td valign="top">
> </cfif>
>
> #CategoryName#<br>
> <cfoutput>
> #SubCategoryName# |
> </cfoutput>
>
> <br><br>
> </cfoutput>
> </td>
> </tr>
> </table>
>
>
>
> Again, haven't tested this or anything... hope it helps.
>
>
> Courtney E. Payne, Developer
> Figleaf Software
> "We've got you covered"
> [EMAIL PROTECTED]
> www.figleaf.com
>
>
> -----Original Message-----
> From: Hassan Khawaja [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 06, 2000 12:45 PM
> To: [EMAIL PROTECTED]
> Subject: MULTIPLE COLUMN output problem!
>
>
> Hi all,
> I am experiencing a certain problem, and will try to explain it as well as
I
> can.
> I have a list of links that I am trying to output in a 2 column format,
very
> similar to Yahoo! main menu design. As you know there are main Categories
> listed with sub categories right underneath them. kind of like:
>
> News Computers
> - World, US.. -Software, WWW...
>
> Business Reference
> - B2B, Companies.. - Encyclopedias, Lists..
>
>
> Now, I have in my database 2 tables - categories, and sub-categories,
which
> are linked together thru a category id. The idea is to ouput the
categories
> with their subcategories with them in a 2 column format.
>
> I can output 2 columns correctly if I only ouput the categories. or if I
> just output the subcategory. However it gets all very hairy if join the
> tables and output using the GROUP parameter of CFOUTPUT.
>
> The problem arises when there are a dissimilar number of subcategories for
> each category. In my code it looks for <cfif currentrow MOD columncount eq
> 0> to determine if the current row's MOD 2 is 0 to know if 2 columns have
> already been output, but when there are different no. of subcategories,
this
> logic statement gets evaluated at the wrong point than the one I want it
to.
> Right now it is printing 3 columns for one row and only one column for
> another, while the rest of the rows are alright with 2 columns.
>
> What I mean is that if a certain category has 1 sub-cat. and the other has
2
> sub-cats, the output counts them as 3 rows total and instead of starting a
> new row after the category and its sub-cats have been output, it tries to
> start a new one as soon as 2 columns have been evaluated. So even though
> the 3rd row is still part of the main category, for the logic statement,
> it's a NEW row.
>
> Sample output: [ numbers are rowcount ]
> --------------------------------------------------------------------------
--
> ----
> 1-Administration 2-Daily Reports
> 1- Areas | 2- Divisions |
>
> 3-Data Entry 4-Help
> 3- Products | 4- About |
>
> 5-MDS 6-MSDS
> 5- Areas | 6- Facilities |
>
> 7-Process Control 9-Production 10-Reports
> *******problem****
> 7- Business | 8- Divisional | 9- Reports | 10-Data |
> *** 3 columns****
>
> 11-SSOP 12-Safety
> 11- Tasks | 12- Data |
>
> 13-Sales ***** problem****
> 13- Facilities | ****just 1 column***
>
>
>
> --------------------------------------------------------------------------
--
> ----
>
>
> To stop confusing you further, I am including the code snippet to
accomplish
> this:
>
> <!--- the query to gather the info --->
> <cfquery name="mainpage">
> SELECT mnu_categories.category_id,
> mnu_categories.category_name,
> mnu_categories.category_url,
> mnu_sub_categories.sub_cat_id,
> mnu_sub_categories.sub_cat_name
>
> FROM mnu_categories, mnu_sub_categories
> WHERE mnu_categories.category_id = mnu_sub_categories.category_id
> ORDER BY
> mnu_categories.category_name,
> mnu_sub_categories.sub_cat_name
> </cfquery>
> <!--- end query--->
>
> <!--- set vars --->
> <cfset columncount = 2>
> <cfset MaxRows = mainpage.recordcount>
> <!---/ set vars --->
>
> <!--- output--->
>
> <table >
> <tr>
> <cfoutput query="mainpage" startrow="1" maxrows=#MaxRows#
> group="category_id">
> <td>
> <a href="index-orig.cfm?action=cat.cfm&c=#category_id#">
> #currentrow#-<b>#category_name#</b></a> <!--- current row output for
> debugging--->
> <br>
> <cfoutput>
> <font face="Arial" size="-2">#currentrow#-
> <a
> href="index-orig.cfm?action=subcat.cfm&c=#category_id#&s=#sub_cat_id#">
> #sub_cat_name#</a> | </font>
> </cfoutput>
> </td>
> <cfif currentrow MOD columncount EQ 0>
> </tr>
> <cfif currentrow NEQ MaxRows>
> <tr bgcolor="FFFFFF" >
> </cfif>
> <cfelseif currentrow EQ MaxRows>
> <cfloop FROM="#Evaluate(columncount-(currentrow MOD
> columncount))#" TO="1" STEP="-1" INDEX="i">
> <td></td>
> </cfloop>
> </tr>
> </cfif>
> </cfoutput>
>
> </table>
>
> I would really appreciate if anyone can help me with this. The code should
> be able to take on any no. of subcategories and out put them nicely
without
> breaking.
>
> Hassan Khawaja
>
> -----------------------------------------------
>
>
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
