I need help with grouping a coldfusion query. I seem to be having some issues with getting the right result

Hello,

I need some help with grouping a coldfusion query. I seem to be having some issues with getting the right results.

table user_cats_main

category id | account_id | category_name | sort_order
1 , 1, My Category 1, 0
2 , 1, My Category 2, 0
3 , 1, My Category 3, 0

table user_sub_cats

record_id | account_id | category_id_rel | sort_order
1, 1, 1, 1
2, 1, 2, 1
3, 1, 1, 2

<cfquery name="getCats" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
	SELECT *, MIN(t1.sort_order) AS t1order, MIN(t2.sort_order) AS t2order
	FROM user_cats_main t1
	LEFT JOIN user_cats_sub t2
	ON t1.category_id = t2.category_id_rel
	WHERE t1.account_id = '#session.account_id#'
	GROUP BY t1.category_id, t2.record_id
	ORDER BY t1order ASC, t2order ASC
</cfquery>

<cfoutput query="getCats" group="category_id">

Category Name: #getCats.category_name# | Category ID: #getCats.category_id#<br>

<cfoutput>
	Sub-Category Record ID | #record_id#<br><br>
</cfoutput>

</cfoutput>

Select all Open in new window

But what I'm getting is wrong...

Category Name: My Category 3 | Category ID: 3
(would show category but no sub category value would be here)

Category Name: My Category 1 | Category ID: 1
Sub-Category Record ID | 1

Category Name: My Category 2 | Category ID: 2
Sub-Category Record ID | 2

Category Name: My Category 1 | Category ID: 1
Sub-Category Record ID | 3


This way works but It's not optimal...

<cfquery name="getCats" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
	SELECT *
	FROM user_cats_main
	WHERE account_id = '#SESSION.account_id#'
	ORDER BY sort_order
</cfquery>


<cfoutput query="getCats">

	#getCats.category_name# | #getCats.category_id#<br>

	<cfquery name="getSubCats" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
		SELECT *
		FROM user_cats_sub
		WHERE account_id = '#SESSION.account_id#'
		AND category_id_rel = #getCats.category_id#
		ORDER BY sort_order
	</cfquery>

	<cfloop query="getSubCats">
		Sub-Category Record ID | #record_id#<br>
	</cfloop>
<br><br>
</cfoutput>

Select all Open in new window

I get....

Category Name: My Category 1 | Category ID: 1
Sub-Category Record ID | 1
Sub-Category Record ID | 3

Category Name: My Category 2 | Category ID: 2
Sub-Category Record ID | 2

Category Name: My Category 3 | Category ID: 3