如何从另一个表中具有匹配ID的另一个表中获取名称?
我在我的网站中使用带有 php 的 sql server 2008 r2.我有两张桌子.
I am using sql server 2008 r2 with php in my website. I have 2 tables.
1 是给员工的.
(int) (nvarchar) (nvarchar)
id name type
1 john 2
2 peter 1
3 leah 2
4 frank 1
5 tang 3
2 是为了工作
(int) (nvarchar) (nvarchar)
workid name employees
1 task1 1,3
2 task2 2,3
3 task3 1,3,4
4 task4 2
我想进行查询，该查询为我提供带有员工姓名的工作描述，其中类型
I want to make query which give me work description with employee name where type < 3.
意味着我想得到这样的结果.
Means i want to get result like this.
workid name employee
1 task1 john, leah
2 task2 peter, leah
3 task3 john,leah,frank
很聪明
那么我怎样才能用 sql 查询达到这个结果?
so how can i achieve this result with sql query ?
我无法更改表架构.
我尝试使用 case when 语句，但它不起作用.
i tried to use with case when statement but its not working.
请帮我解决这个问题..
Please help me to get this working..
本文的内容并不能完全回答问题，但它会建议您如何正确规范表格以简化问题.
The content of this doesn't totally answers the question but it will suggest on how you can properly normalize the table in order for theproblem to be simplified.
这是一个多对多关系.
Employees
- ID (Primary Key)
- Name
- Type
Task
- ID (Primary Key)
- Name
Work
- EmployeeID (Foreign Key)
- TaskID (Foreign Key)
员工表
id name type
1 john 2
2 peter 1
3 leah 2
4 frank 1
5 tang 3
任务表
id name
1 task1
2 task2
3 task3
4 task4
工作台
TaskID EmployeeID
1 1
1 3
2 2
2 4
3 1
3 2
3 3
4 4
查询，
SELECT t.ID, t.Name,
STUFF(
(SELECT ',' + b.Name
FROM Work a
INNER JOIN Employee b
ON a.EmployeeID = b.ID
WHERE a.TaskID = t.ID
FOR XML PATH (''))
, 1, 1, '') AS NamesList
FROM Task t
-- WHERE ..... -- add additional conditions...
GROUP BY t.ID, t.Name