help with sql, my brain has frozen

OK, here is what i have currently as an output from a query: JaneDoe  Department Home Page Maintenance  76 BillSmith  Department Home Page Maintenance  71,79 etc... This is a list of employees with rights to parts of our intranet, specifically the ones who can update department content. Here is the SQL: SELECT      e.LastName, e.FirstName, i.MenuItemName, m.DepartmentID FROM         dbo.AdminUser a, dbo.tblEmployee e, dbo.tblEmployeeMenuItemDepartment m, dbo.tblAdminMenuItem i WHERE      a.employeeID = e.EmployeeID AND                   e.EmployeeID = m.EmployeeID AND                   m.AdminMenuItemID = i.AdminMenuItemID AND                   (i.AdminMenuItemID = 1) The DepartmentID is a concatenated list of the departments which the employee can admin. How do I get a list of the actual names, which are in tblDepartment, in the field DepartmentName, so that my output looks like: JaneDoe  Department Home Page Maintenance  Marketing BillSmith  Department Home Page Maintenance  Accounting BillSmith  Department Home Page Maintenance  Law etc... I know I should be able to do this, but I just can't get my mind around it. Thanks. Kathryn Butterly Web Developer Washington Mutual Finance 813 632-4490 kbutterly@wmfinance.com ______________________________________________________________________ Macromedia ColdFusion 5 Training from the Source   Step by Step ColdFusion   http://www.amazon.com/exec/obidos/ASIN/0201758474/houseoffusion

You have a problem with your database structure.  The columnn DepartmentID should not be a concatenated list of departments, but rather a table which has one row for each department a particular employee can admin.  In essence you have a string with a set of keys in it, and you're trying to turn it back into a usable key.  In the short term it makes getting this report difficult, In the long term it's going to be a problem when you actually start to try and use the key for security. OK, here is what i have currently as an output from a query: JaneDoe  Department Home Page Maintenance  76 BillSmith  Department Home Page Maintenance  71,79 etc... This is a list of employees with rights to parts of our intranet, specifically the ones who can update department content. Here is the SQL: SELECT      e.LastName, e.FirstName, i.MenuItemName, m.DepartmentID FROM         dbo.AdminUser a, dbo.tblEmployee e, dbo.tblEmployeeMenuItemDepartment m, dbo.tblAdminMenuItem i WHERE      a.employeeID = e.EmployeeID AND                   e.EmployeeID = m.EmployeeID AND                   m.AdminMenuItemID = i.AdminMenuItemID AND                   (i.AdminMenuItemID = 1) The DepartmentID is a concatenated list of the departments which the employee can admin. How do I get a list of the actual names, which are in tblDepartment, in the field DepartmentName, so that my output looks like: JaneDoe  Department Home Page Maintenance  Marketing BillSmith  Department Home Page Maintenance  Accounting BillSmith  Department Home Page Maintenance  Law etc... I know I should be able to do this, but I just can't get my mind around it. Thanks. Kathryn Butterly Web Developer Washington Mutual Finance 813 632-4490 kbutterly@wmfinance.com ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.

Thanks for the reply.  I got my data out using a cfquery and a cfloop.  I know that the design is awful; I am a fiend on the subject of proper database design.  Unfortunately, I have no control over this table's design. Thanks for the reply. Kathryn Butterly Web Developer Washington Mutual Finance 813 632-4490 kbutterly@wmfinance.com You have a problem with your database structure.  The columnn DepartmentID should not be a concatenated list of departments, but rather a table which has one row for each department a particular employee can admin.  In essence you have a string with a set of keys in it, and you're trying to turn it back into a usable key.  In the short term it makes getting this report difficult, In the long term it's going to be a problem when you actually start to try and use the key for security. OK, here is what i have currently as an output from a query: JaneDoe  Department Home Page Maintenance  76 BillSmith  Department Home Page Maintenance  71,79 etc... This is a list of employees with rights to parts of our intranet, specifically the ones who can update department content. Here is the SQL: SELECT      e.LastName, e.FirstName, i.MenuItemName, m.DepartmentID FROM         dbo.AdminUser a, dbo.tblEmployee e, dbo.tblEmployeeMenuItemDepartment m, dbo.tblAdminMenuItem i WHERE      a.employeeID = e.EmployeeID AND                   e.EmployeeID = m.EmployeeID AND                   m.AdminMenuItemID = i.AdminMenuItemID AND                   (i.AdminMenuItemID = 1) The DepartmentID is a concatenated list of the departments which the employee can admin. How do I get a list of the actual names, which are in tblDepartment, in the field DepartmentName, so that my output looks like: JaneDoe  Department Home Page Maintenance  Marketing BillSmith  Department Home Page Maintenance  Accounting BillSmith  Department Home Page Maintenance  Law etc... I know I should be able to do this, but I just can't get my mind around it. Thanks. Kathryn Butterly Web Developer Washington Mutual Finance 813 632-4490 kbutterly@wmfinance.com ______________________________________________________________________ Macromedia ColdFusion 5 Training from the Source   Step by Step ColdFusion   http://www.amazon.com/exec/obidos/ASIN/0201758474/houseoffusion