major join?

[ranger]

what i am trying to do to one poor table is:
.................................................. .....get all
departments....................................... ..............................
.................................. <cfquery name='getdepts'
datasource='survey_table'> SELECT DISTINCT(empDepartment) AS sdept FROM
#tblnm# </cfquery> ...........................use output from getdepts to get
count of how many people in each
department........................................ ...... <cfoutput
query='getdepts'> <cfset deptlst = '#sdept#'> <cfquery name='gtdptcnt'
datasource='survey_table'> SELECT COUNT(empDepartment) AS dptctz FROM
#tblnm# WHERE empDepartment = '#deptlst#' ; </cfquery>
.................................................. ..find out how many people in
each department fall into a certain category.................................
<cfquery name='gtdptcnt1' datasource='survey_table'> SELECT
COUNT(empDepartment) AS dptctz1 FROM #tblnm# WHERE (empDepartment =
'#deptlst#') AND (memdt <> 0) ; </cfquery>
.................................................. .........list departments, how
many people/how many in category, percent in category...................... is
there any way to do this as a join... keep getting messages that will only
report one entry, etc, etc. one table has 32 departments and 350 employees...
could be much larger.... churn, chur.... poor server. tnx in advance.

[DGH]

I would like to help, but I'm a bit befuddled by your variable table name, so
excuse me if I'm way off track. Would it help to use 'group by' to get counts
of employees within each department? Ex (query1): <cfquery name='query1'
datasource='????'> select count(employeeID) as empsindepartment, Department
from employeetable group by Department order by Department </cfquery> Then
loop over the departments? <cfloop query = 'query1'> <cfquery name='query2'
datasource ='???'> select count(employeeID) as empsincategory from
employeetable where Department=#query1.Department# and Category=whatever
</cfquery> Calculate percentages using query1.empsindepartment and
query2.empsincategory and output row for query1.department </cfloop> There
are faster ways to do this, but this is the simplest way I know, and it
shouldn't eat your server. (If I'm getting what your asking).