Get Names from Id
-
I have two Tables LIKE Emp(EmpNo,EmpName,DeptNo),Dept(No,Name). Emp Table looks like: 1 xxx 1,2 2 yyy 2,3 3 zzz 1 Dept Table: 1 aaaa 2 bbbb 3 cccc My Output will be: 1 xxx aaaa,bbbb 2 yyy bbbb,cccc 3 zzz aaaa How can I do this in Sqlserver2005
-
I have two Tables LIKE Emp(EmpNo,EmpName,DeptNo),Dept(No,Name). Emp Table looks like: 1 xxx 1,2 2 yyy 2,3 3 zzz 1 Dept Table: 1 aaaa 2 bbbb 3 cccc My Output will be: 1 xxx aaaa,bbbb 2 yyy bbbb,cccc 3 zzz aaaa How can I do this in Sqlserver2005
Your database model is faulty IMAO. How about:
Emp
EmpID Empname
1 xxx
2 yyy
3 ZZZDept
DeptID Deptname
1 aaaa
2 bbbb
3 ccccEmp_Dept
EmpID DeptID
1 1
1 2
2 2
2 3
3 1then you can use
SELECT EmpName,DeptName
FROM Emp e
Join EmpDept ed
ON e.EmpID = ed.EmpID
JOIN Dept d
ON ed.Deptid = d.DeptIDAnd if you really need to get the deptnames as commaseparated values you can have a look in this thread[^] how to do that.
"When did ignorance become a point of view" - Dilbert
-
Your database model is faulty IMAO. How about:
Emp
EmpID Empname
1 xxx
2 yyy
3 ZZZDept
DeptID Deptname
1 aaaa
2 bbbb
3 ccccEmp_Dept
EmpID DeptID
1 1
1 2
2 2
2 3
3 1then you can use
SELECT EmpName,DeptName
FROM Emp e
Join EmpDept ed
ON e.EmpID = ed.EmpID
JOIN Dept d
ON ed.Deptid = d.DeptIDAnd if you really need to get the deptnames as commaseparated values you can have a look in this thread[^] how to do that.
"When did ignorance become a point of view" - Dilbert
Ignore this response - it was wrong. Monday morning syndrome. Do you work in 2 departments? I admit that some corporate structures work in this way, but for the purpose of the OP's question it was unecessary to add a many-to-many relationship between employees and departments. His original structure of 1 department hads many employees, ie a one-to-many relationship (or to put this another way each employee works in just 1 department) would have worked just fine, with just one join in the query.
modified on Monday, August 9, 2010 7:55 AM
-
I have two Tables LIKE Emp(EmpNo,EmpName,DeptNo),Dept(No,Name). Emp Table looks like: 1 xxx 1,2 2 yyy 2,3 3 zzz 1 Dept Table: 1 aaaa 2 bbbb 3 cccc My Output will be: 1 xxx aaaa,bbbb 2 yyy bbbb,cccc 3 zzz aaaa How can I do this in Sqlserver2005
Ignore this answer. I mis-interpreted your source data. The above answer, although correct confuses your question somewhat. All you need is to join employee table to department table using the foreign/primary key between employee and department. SELECT EmpNo, EmpName, DeptName FROM Emp INNER JOIN Dept ON Emp.DeptNo=Dept.No
modified on Monday, August 9, 2010 7:54 AM
-
Ignore this response - it was wrong. Monday morning syndrome. Do you work in 2 departments? I admit that some corporate structures work in this way, but for the purpose of the OP's question it was unecessary to add a many-to-many relationship between employees and departments. His original structure of 1 department hads many employees, ie a one-to-many relationship (or to put this another way each employee works in just 1 department) would have worked just fine, with just one join in the query.
modified on Monday, August 9, 2010 7:55 AM
No, if you look at the example data in the OP, it is a many-to-many relationship. Employee xxx works in two departments, aaaa and bbbb. Department aaaa has two employees, xxx and zzz.
-
No, if you look at the example data in the OP, it is a many-to-many relationship. Employee xxx works in two departments, aaaa and bbbb. Department aaaa has two employees, xxx and zzz.
-
Ignore this response - it was wrong. Monday morning syndrome. Do you work in 2 departments? I admit that some corporate structures work in this way, but for the purpose of the OP's question it was unecessary to add a many-to-many relationship between employees and departments. His original structure of 1 department hads many employees, ie a one-to-many relationship (or to put this another way each employee works in just 1 department) would have worked just fine, with just one join in the query.
modified on Monday, August 9, 2010 7:55 AM
J4amieC wrote:
Monday morning syndrome.
I know everything about that, except it usually happens to me any day of the week
"When did ignorance become a point of view" - Dilbert
-
Ignore this answer. I mis-interpreted your source data. The above answer, although correct confuses your question somewhat. All you need is to join employee table to department table using the foreign/primary key between employee and department. SELECT EmpNo, EmpName, DeptName FROM Emp INNER JOIN Dept ON Emp.DeptNo=Dept.No
modified on Monday, August 9, 2010 7:54 AM
TRY THIS: SELECT Emp.EmployeeID, Emp.FirstName, STUFF((SELECT ',' + DeptName FROM Dept WHERE Dept.DeptID = Emp.DeptID FOR XML PATH('')),1,1,'') AS DeptNames FROM Employees Emp