Join table with different datatype

Question

I have following tables:

Orders
  id int
  orderName varchar(5000)

Communication
  body varchar(5000)
  attachment varchar(5000)

Sample Orders data:

id   name
132  ordGD
589  ordPG
6321 ordMF

Sample Communication data:

body    attachment
body1   132,589,6321

I want to create a stored procedure that get the 2 columns {body,Attachment} from Communication

In the SP, the input @attachment varchar(5000) contains multiple orderid, comma separated that references the Orders table

Problem : i want OrderName according to Orderid from Orders matched to @Attachment

Your question is really hard to understand. Can you please provide the schema (names and types for the columns in the involved tables) and some sample input data and expected output data? — PHeiberg, Oct 04 '12 at 14:01
Can't be done as a join. Split the data up and store it atomically instead of combining it together. Otherwise, read the data with a cursor, parse it and store the matching order records in a temporary table or a table variable. But don't expect good performance. — Jeff Siver, Oct 04 '12 at 14:01
Table : Orders column : orderid column : ordername __table : communication Column:body Column:Attachment — Ravi Makadia, Oct 04 '12 at 14:03
attachment have orderids like > 132,589,6321 and i want to convert orderids to respective ordernames like >ordGD,ordPG,ordMF — Ravi Makadia, Oct 04 '12 at 14:07

Taryn · Answer 1 · 2012-10-04T20:12:04.050

For this type of process, you need to split the data that is in the attachment column. I use something similar to this to Split strings (there are many ways to split strings, you can search online for other functions):

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin     
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return      

end

Since this returns a table, you can join on the data. So your query would look like this:

select o.id,
  o.name,
  c.body
from orders o
left join 
(
  select c.body, s.items as o_id
  from communications c
  cross apply dbo.split(c.attachment, ',') s
) c
  on o.id = c.o_id

See SQL Fiddle With Demo

If you want to just replace the values from the attachment field with the correct names, you can use the Split function and CTE in one step:

;with cte as (
  select o.id,
    o.name,
    c.body
  from orders o
  left join 
  (
    select c.body, s.items as o_id
    from communications c
    cross apply dbo.split(c.attachment, ',') s
  ) c
    on o.id = c.o_id
)
select distinct c2.body,
  stuff((select distinct ', ' + c1.name
         from cte c1
         where c2.body = c1.body
         for XML path('')),1,1,'') attachment
from cte c2

See SQL Fiddle with Demo

score 1 · Answer 2 · edited May 23 '17 at 12:18

As others have commented the correct solution is to change the database schema to have a normalized schema with atomic fields.

A solution for the current schema is to first split the attachment field into a list of order numbers using any of the multiple ways available to do it. The next step is to join the result with the Orders table on the id to get the results. The third step is to concatenate the names back into a list. See the links provided for explanations of split and join.

Here is a snippet performing the 3 steps:

WITH
-- Numbers table for split logic
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3),

-- The join query for step 2
bodyOrders AS
(SELECT body,        
       o1.name orderName
FROM   Numbers AS nums 
INNER JOIN Communication AS valueTable 
ON nums.n <= CONVERT(int, LEN(valueTable.attachment)) 
AND SUBSTRING(N',' + valueTable.attachment, n, 1) = N','
INNER JOIN Orders o1
ON LTRIM(RTRIM(SUBSTRING(valueTable.attachment, nums.n, 
   charindex(N',', valueTable.attachment + N',', nums.n) - nums.n))) = o1.id
)

-- Concatenation logic for step 3
SELECT  body,
stuff( (SELECT ','+ orderName
               FROM bodyOrders b2
               WHERE b2.body = b1.body 
               ORDER BY orderName
               FOR XML PATH(''), TYPE).value('.', 'varchar(5000)')
            ,1,1,'')
       AS orderNumbers
      FROM bodyOrders b1
      GROUP BY body;

This snippet isn't providing an optimal or performant way to do this for large data sets. It's just an example of how it might be done if you must go down this path.

Example SQL Fiddle.

Join table with different datatype

2 Answers2