<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>
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>