how to get name from another table with matching id in another table?

Question

I am using sql server 2008 r2 with php in my website. I have 2 tables.

1 is for employees.

(int)      (nvarchar)   (nvarchar)

id         name        type
 1         john         2
 2         peter        1
 3         leah         2
 4         frank        1
 5         tang         3

2 is for work

(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

like wise

so how can i achieve this result with sql query ?

I can not change in table schema.

i tried to use with case when statement but its not working.

Please help me to get this working..

why can't u change the table schema? that's really f*cked up! you store an array of integers as string. Databases are not designed to do searches of that kind. So you've got to use a lot of very expensive operations to get things runnning. You should at least(!) turn that column into a `SET`. ... . Much better: Look at JW.'s post! — Benjamin M, Mar 04 '13 at 03:18
Assuming you can't change your table structure, take a look at my post -- should handle your needs. If you can change your table structure, look at JW's answer as it's better for normalization. I just didn't want to presume you could change your db structure. — sgeddes, Mar 04 '13 at 03:50

John Woo · Accepted Answer · 2013-03-04T03:44:15.600

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.

This is a Many-to-Many Relationship.

Employees
- ID (Primary Key)
- Name
- Type

Task
- ID (Primary Key)
- Name

Work
- EmployeeID (Foreign Key)
- TaskID (Foreign Key)

EMPLOYEE TABLE

id         name        type
 1         john         2
 2         peter        1
 3         leah         2
 4         frank        1
 5         tang         3

TASK TABLE

 id         name        
  1         task1       
  2         task2       
  3         task3       
  4         task4

WORK TABLE

TaskID  EmployeeID
1           1
1           3
2           2
2           4
3           1
3           2
3           3
4           4

Query,

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 Demo

Thanks JW. I asked client to change the schema and normalize the table. — Mausami, Mar 08 '13 at 02:05

score 0 · Answer 2 · answered Mar 04 '13 at 03:36

Here's one way you can split a comma delimited list using For XML:

SELECT w.workid, w.name, 
  STUFF((
   SELECT ',' +  E.Name AS [text()]
    FROM  (
      SELECT A.workid,  
      Split.a.value('.', 'VARCHAR(100)') AS EmpId
      FROM  
      (SELECT workid,  
       CAST ('<M>' + REPLACE(employees, ',', '</M><M>') + '</M>' AS XML) AS String  
       FROM  work
      ) AS A 
      CROSS APPLY String.nodes ('/M') AS Split(a)
    ) A 
    JOIN employees E ON A.EmpId = E.Id
    WHERE WorkId = w.WorkId
    FOR XML PATH('')
  ), 1, 1, '') AS Employees
FROM work w

SQL Fiddle Demo

This results in:

WORKID   NAME    EMPLOYEES
1        task1   john,leah
2        task2   peter,leah
3        task3   john,leah,frank
4        task4   peter

Thanks sgeddes. Your answer will work. but i asked to client to change the table schema to normalize the structure so my problem is solved. Thanks a lot once again. — Mausami, Mar 08 '13 at 02:04

how to get name from another table with matching id in another table?

2 Answers2

Linked