Which database server are you using? Some allow you the ability to return an aggregate of values in the form of a comma delimited list. You might be able to get your result set to look like this:
EMPID NAME OFFICES 1 Dave Boston (NE), Dallas (SW), Miami (SE) 2 John San Diego (W), New Orleans (S) Is that the kind of result set you are looking for? Dave -----Original Message----- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Friday, October 02, 2009 11:10 AM 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:326847 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
