QofQ and Nesting

[Guest]

I'm using a QofQ in order to find subcategories from my database. The database is made up as follows;

ID, categoryname, subcategoryof

Same sample data would be as follows;

1, Category1,(null)
2, Subcategory, 1

The intended result is to have the following output;

Category1
- Subcategory

This will be presented in a select box as part of a form.

However, what I'm getting at the moment is the following;

Subcategory
Category1
Subcategory
Category1
Subcategory
Category1

(the same ones repeated 3 times, in that order and without the '-' before the subcategories).

The code I have at the moment is as follows (and yes, I have no doubt this isn't the most elegant method to use!);

[Guest · Accepted solution]

I have figured it out - I had an unnecessary cfoutput and my subcategory query was doing a query against the wrong result set.

[Dan_Bracuk]

First, use a self join in your query

Next use the group attribute of the cfquery tag on the parent category.