Extracing records from a table by joining with another table

Question

Hi All.

I have a table setup similar to the below

A job table which illustrates jobs

There is an employee table.

The employee id column and the resource_id column both mean the same thing, I will like to extract the names assigned to every job and the name of the resource assigned to help on the job. In the case of job 1234, its assigned to Tom and a resource id 33 which doesn't existing on the employee table, I want this to return null. In the case of job 1236, the employee name assigned is William and Tom is assigned as an extra resource to help. In the case of job 1237, Emily and George are the assigned resources to help, this will be comma delimited.

create table #jobs  
(  
jobid int,  
employee_id int,  
resource_id varchar(50),  
hours smallint  
)  
  
create table #employees  
(  
employee_id int,  
employee_name varchar(200)  
)  
  
  
insert into #jobs (jobid, employee_id, resource_id, hours)  
select 1234,44,'33',2  
union all  
select 1235,45,'22',3  
union all  
select 1236,47,'44',5  
union all  
select 1237,44,'45,46',7  
union all  
select 1238,46,'47',1  
  
  
insert into #employees  
select 44,'Tom'  
union all  
select 45,'Emily'  
union all  
select 46,'George'  
union all  
select 47,'William'  
  

Answer 1

Hi @MrFlinstone ,

For versions before 2016, need to create a custom function to merge and separate strings.Please refer to the following method:

CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))       
RETURNS @result TABLE(F1 VARCHAR(100))       
  AS         
   BEGIN       
   DECLARE @sql AS VARCHAR(100)       
  SET @Sourcestr=@Sourcestr+@Seprate         
  WHILE(@Sourcestr<>'')       
  BEGIN       
    SET @sql=left(@Sourcestr,CHARINDEX(',',@Sourcestr,1)-1)       
    INSERT @result VALUES(@sql)       
     SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(',',@Sourcestr,1),'')       
   END       
   RETURN    
   END  
 GO  
  
;WITH cte  
as(SELECT jobid,employee_id,F1 as resource_id  
FROM #jobs s   
CROSS APPLY SplitStr(S.resource_id,',') V)  
,cte2 as  
(SELECT c.*,e.employee_name FROM cte c  
join #employees e  
ON c.employee_id=e.employee_id)  
,cte3 as  
(SELECT c2.*,e.employee_name as help FROM cte2 c2  
left join #employees e  
ON c2.resource_id=e.employee_id)  
  
SELECT jobid,employee_name  
       , STUFF((SELECT ',' + CAST(help AS VARCHAR(30)) AS [text()]  
          FROM cte3 AS O  
          WHERE O.jobid = C.jobid   
          FOR XML PATH('')), 1, 1, NULL) as help  
FROM cte3  as c  
GROUP BY jobid,employee_name  
ORDER BY jobid  

Output:

jobid   employee_name  help  
1234 Tom            NULL  
1235 Emily        NULL  
1236 William        Tom  
1237 Tom            Emily,George  
1238 George        William  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try a query for modern SQL:

select jobid, 
    e.employee_name as assigned_employee,
    (select string_agg(employee_name, ', ') 
     from #employees 
     where employee_id in ( select value from string_split(j.resource_id, ','))) as help
from #jobs j
left join #employees e on e.employee_id = j.employee_id