Les, I think the query as is will do what you want. Instead of fixing the query use the Grouping function of cfoutput to only display the employee name once, but the other data as it is.
Steve -----Original Message----- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Friday, October 02, 2009 12:10 PM To: cf-talk Subject: GROUP BY problem... SELECT employees.empID employees.name, employees_office.theORDER office.office_name areas.area_NAME FROM employees INNER JOIN employees_office ON employees.empID = employees_office.empID INNER JOIN office ON employees_office.id_office = dbo.office.office_id INNER JOIN areas ON areas.empID = employees_office.empID ORDER BY employees.name, employees_office.theORDER OK, here's the problem I need to be able to "GROUP BY" employees.empID so each employee is returned only ONCE (a unique empID), but, I can't include any of the columns in the other tables in the "GROUP BY", because there may be multiple records from those tables. An employee may exist in more than one city and may also be assigned to many areas. So, get the employee only ONCE, but return multiples from the other joined tables. Obviously using just "GROUP BY empID" get us the "columns not included in aggregrate" error. I'm not sure how to fix this. I'd like to do it one query, not multipe queries... Advice? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326839 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4