如何从另一个表中获取具有匹配 id 的另一个表中的名称? [英] how to get name from another table with matching id in another table?

问题描述

我在我的网站中使用带有 php 的 sql server 2008 r2.我有 2 张桌子.

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

我想查询给我工作描述的员工姓名，其中 type <3.

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 ?

我无法更改表架构.

我尝试使用 with 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.

p>

这是一个多对多关系.

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

• SQLFiddle 演示

这篇关于如何从另一个表中获取具有匹配 id 的另一个表中的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！