0

I have a list of employees (EmployeeID, EmployeeName, etc) I have a list of Projects (ProjectID, ProjectName, etc) This is a many to many relationship - a project can have many employees working on it and an employee can be working on many projects.

This much is okay. Currently I've done

SELECT Employee.EmployeeID, ProjectEmployee.ProjectID
FROM Employee LEFT JOIN
ProjectEmployee ON Employee.EmployeeID = ProjectEmployee.EmployeeID LEFT JOIN
Projects ON ProjectEmployee.ProjectID = Projects.ProjectID

This currently returns all employees whether or not they are assigned to a project. If they are assigned to multiple projects, their ID appears in column 1, twice. e.g.

EmployeeID  ProjectID
1           NULL
2           1
3           1
4           1
4           2
5           1
6           2
7           2

I don't know if it's possible but I'd like it to list all employees once in column one. If they are assigned to multiple projects, list all the projects in a single field in column 2 e.g.

EmployeeID  ProjectID
1           NULL
2           1
3           1
4           1,2
5           1
6           2
7           2

Thanks in advance for any advice!

hero9989
  • 65
  • 1
  • 1
  • 11
  • 3
    http://stackoverflow.com/a/1785923/215752 – Hogan Dec 01 '16 at 17:00
  • Sure it's possible, but then it requires the client to split it if they want to use them separately. Typically it's easier for clients to get separate rows and concatenate when displayed. SQL is not designed to concatenate multiple rows like that. – D Stanley Dec 01 '16 at 17:02
  • 1
    @DStanley, that's a bit to strict. SQL is doubtlessly designed to fill reports, lists, printable documents... – Shnugo Dec 01 '16 at 17:07
  • @Shnugo I'm just saying that concatenating values like that is not easy in SQL (just look at the duplicate answer and the multitude of similar questions on SO). It's generally much easier to group and format in reports, lists, document creation, etc. And if the client wants to find all employees in Project 2 from that data set it's even more difficult. SQL is designed for raw data, not presentable information. – D Stanley Dec 01 '16 at 17:15
  • @DStanley, Okay, adding ProjectIDs to a comma separated list might be something one should re-think... – Shnugo Dec 01 '16 at 17:20

0 Answers0