[thelist] CF - multiple groups, and SQL too perhaps

hi,

i'm trying to do multiple groups within a CF application, but not having much
luck. the solution might be something simple in CF or something within my query
that i should try.

anyway, here's the relevant query:

	<cfquery name="getreviews" datasource="#db#">
	select *
	from review, auction, location, member
	where locationactive = 1
	and auctionactive = 1
	and reviewapproved = 1
	and review.auctionid = auction.auctionid
	and auction.locationid = location.locationid
	and review.memberid = member.memberid
	order by locationname, auctiontitle, reviewdate;
	</cfquery>


(rudy, how bad is my SQL?)

im grabbing some stuff from the member table (names of the review author, etc).
the other tables are the important ones though.

the situation is this: i have multiple locations. each location has multiple
auctions. each auction has multiple reviews. i'm hoping to display the resulting
data like so:

	 - location1
	     - auction1
	        - review1
	     - auction2
	        - review2
	        - review3
	        - review4
	 - location2
	     - auction3
	        - review5
	        - review6
		    etc

essentially, it's a 3 level <ul>

now, 2 level is easy:

	<ul>
	<cfoutput query=getreviews group=auctionid>
	<li><b>#auctiontitle#</b></li>
	<ul>

	<cfoutput>
	<li><a href="reviewdetails.cfm?reviewid=#reviewid#">#reviewtitle#</a>
	(by <cfif #len("#reviewnameoverride#")# is 0>
	#memberfirstname# #memberlastname#<cfelse>#reviewnameoverride#</cfif>
	on #dateformat("#reviewdate#","dd/mm/yyyy")#)</li>
	</cfoutput>

	</ul>
	</cfoutput>
	</ul>

but what about 3 level?

i can't put a <cfoutput group=locationid> around the above code because CF only
allows the one group, right? and i can't nest <cfoutput>'s unless the outer one
has a group or query attribute. and then a <cfoutput> with a query attribute
cannot be enclosed by another, etc.

any ideas?

should i be using "group by location.locationid" within my sql? something like
that?


tia,


isaac