We help IT Professionals succeed at work.

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

brihol44
brihol44 asked
on
312 Views
Last Modified: 2014-05-14
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>

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>

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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks agx! A simple oversight. Got it! Thx again.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.