nice first post, you provided quite a bit of info.

my first point, is in the future, if you can provide your data as CREATE TABLE / INSERT INTO statements like this, anyone can easily see your data and provide test scripts for you:

drop table tblEmployees

drop table tblCategories

CREATE TABLE tblCategories(CategoryID[INT],CategoryName[VARCHAR](30))

CREATE TABLE tblEmployees(EmployeeID[INT],EmployeeName[VARCHAR](30),CategoryIDs[VARCHAR](30))

INSERT INTO tblCategories( CategoryID,CategoryName)

SELECT '1','AAA' UNION ALL

SELECT '2','BBB' UNION ALL

SELECT '3','CCC'

INSERT INTO tblEmployees(EmployeeID,EmployeeName,CategoryIDs)

SELECT '1','XXX','1,3' UNION ALL

SELECT '2','YYY','2,3' UNION ALL

SELECT '3','ZZZ','1,2'

Next, you want to avoid storing multiple values in a single column, otherwise you run into problems like you are having now.

instead of having '1,2' as a varchar field in a row, you should have two rows in the table, both with foreign keys to the category instead.

a work around for that is to use the DelimitedSpilt8K splitter function here on SSC:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

with that function, you can split that comma-delimited list into seperate rows (like they should have been)

SELECT * FROM tblEmployees

cross apply master.dbo.DelimitedSplit8K(CategoryIDs,',')

then using those results as a sub query, you could join them on category:

select * from tblCategories

LEFT OUTER JOIN ( SELECT * FROM tblEmployees

cross apply master.dbo.DelimitedSplit8K(CategoryIDs,',')

) MyAlias

On tblCategories.CategoryID = MyAlias.Item

hope that helps get you started.