Dowdell, Jason G wrote:
> Here's a bit of a challenge. I have a single table storing category
> information similar to any search engine. The table structure looks
> like this
> ID Name ParentID Level
> 1 Computers 0 1
> 2 Laptop 1 2
> 3 Desktop 1 2
>
> Since I have to display this information I must join the table back to
> itself. All joining has gone fine :) My problem is in the display of the
> information to the users. I want to display the Parent category
> and then only 3 children from each category. Sounds easy but
> I'm not sure if I can do this in sql or not. If sql could accomplish
> this then super, if not then it's a good CF challenge.
SELECT
p.name AS parentname,
c.name AS childname
FROM
category c INNER JOIN category p
ON (c.parentID = p.ID AND p.level = 1)
WHERE
(
SELECT Count(*)
FROM category
WHERE category.parentID = c.parentID
AND category.ID < c.ID
) <= 2;
<cfoutput query="" group="parentname">
#parentname#
<cfoutput>
#childname#
</cfoutput>
</cfoutput>
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
