How to do a join bewteen tables with a conditional statement-CodePudding

I have two tables. I do not have a direct field to join. But, what I want to do, is to take everything in Table1, and join it with a field that is "in" a field in Table2. I know this will result in multiple connections between rows in Table2 to Table1, but that is okay. Let me give a for instance..

Please have some patience. I know there is a way to make a query to do this, but I have not done that before. But, I can give sample data, and what I want to do with it.

Table1(SN, Customer)
Table2(SNRange, OrderNum, Line Item)

Table1[12, Bob,
       13, Bob,
       14, Bob,
       54, Bill,
       55, Bruno,
       56, Bruno,
       100, Sylvia]
Table2[12,13, WO234, 1,
       14, WO234, 2,
       54, WO300, 1,
       55,56, WO532, 1]

Result:(SN, Customer, WONum, LineNum)
      [12, Bob, WO234, 1,
       13, Bob, WO234, 1,
       14, Bob, WO234, 2,
       54, Bill, WO300, 1,
       55, Bruno, WO532, 1,
       56, Bruno, WO532, 2,
       100, Sylvia, NULL, NULL]

My very uneducated guess would be:

SELECT *
From Table1
INNER JOIN SN ON Table1.SN in Table2.SNRange

I think this defines the situation, and loose logic on how to do it. I need some format help. SNRange is a string with commas separating the numbers. I know my formatting is horrible, but I'm not very deep in SQL, but I have needed to do some complex things.

Thank you!! Rod

CodePudding user response：

I can think of two different ways to do this, but one is more accurate than the other.

I've set up a test using the following SQL script:

--Build temp tables
    IF OBJECT_ID('tempdb..#table1') IS NOT NULL
        DROP TABLE #table1;

    IF OBJECT_ID('tempdb..#table2') IS NOT NULL
        DROP TABLE #table2;

    CREATE TABLE #table1 (SN varchar(10), Customer varchar(50));
    CREATE TABLE #table2 (SNRange varchar(20), OrderNum varchar(10), LineItem int);


--Insert values into tables
    INSERT INTO #table1 (SN, Customer)
    VALUES (12, 'Bob'),
        ('13', 'Bob'),
        ('14', 'Bob'),
        ('54', 'Bill'),
        ('55', 'Bruno'),
        ('56', 'Bruno'),
        ('100', 'Sylvia');

    INSERT INTO #table2 (SNRange, OrderNum, LineItem)
    VALUES ('12,13', 'WO234', '1'),
        ('14', 'WO234', '2'),
        ('54', 'WO300', '1'),
        ('55,56', 'WO532', '1');


--View data in tables
    SELECT *
    FROM #table1;

    SELECT *
    FROM #table2;

Version 1 (Accurate depending on Range values)

SELECT one.SN, one.Customer, two.OrderNum As WONum, two.LineItem As LineNum
FROM #table1 one
LEFT JOIN #table2 two ON CHARINDEX(one.SN, two.SNRange)>0;

This returns: | SN | Customer | WONum | LineNum | | :-- | :------- | :-----| :------ | | 12 | Bob | WO234 | 1 | | 13 | Bob | WO234 | 1 | | 14 | Bob | WO234 | 2 | | 54 | Bill | WO300 | 1 | | 55 | Bruno | WO532 | 1 | | 56 | Bruno | WO532 | 1 | | 100 | Sylvia | NULL | NULL |

The CHARINDEX(one.SN, two.SNRange) function determines if the value of one.SN is contained anywhere inside the value of two.SNRange. If it does exist, the function returns the integer value representing position of one.SN in two.SNRange. Otherwise it returns 0.

When will this query return INACCURATE results? Based on the values you have now, this query works. However, if you have values 1 & 12 in the Range column, this will not work because this is not matching the value of one.SN to a whole number in two.SNRange. In other words, all of these will return a value greater than 0:

CHARINDEX('1','1') = 1        --Good match
CHARINDEX('1','12') = 1       --Bad match
CHARINDEX('1', '201') = 3     --Bad match
CHARINDEX('1', '2,201') = 5   --Bad match

Version 2 (always accurate)

WITH
    cteTable2 AS
    (
        SELECT value As SN, t.OrderNum As WONum, t.LineItem As LineNum
        FROM #table2 t
        CROSS APPLY STRING_SPLIT(t.SNRange,',') s
    )

SELECT *
FROM #table1 one
LEFT JOIN cteTable2 two ON one.SN=two.SN

Inside the with statement, the STRING_SPLIT function is key. When you have a field that contains values separated by a delimiter, this function takes those values, splits them by the delimiter, & puts each individual value into its own row.

So, | SNRange | | :------ | | 55, 56 |

becomes | SN | | :- | | 55 | | 56 |

Based on the current values, the query above returns the same results as Version 1. However, if you have SN values like '1' & '101', you will not have the matching problem described in Version 1.

I hope this helps. Best of luck!

CodePudding user response：

@XMarksTheSpot

I defintely went with v2. I did have some thoughts about a false match, but was going to handle that in a subsequent step. Well, I did use this method. However, when I run it, its like it doesnt know what to do with the string_split() command. It does do a auto-match for the command, but then thows an 'Invalid object name' error. What gives?

With cteTemporary_assign_wo as
(
select value as SN, t.OrderNumber As WONum, t.LineNumber as LineNum
From dbo.temporary_assign_wo As t
cross apply string_split(t.SN_List,',') s
)


Select *
from dbo.SN_Test_DF_Idx_2 one
Left join cteTemporary_assign_wo two on one.SN=two.SN

The error I get:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'string_split'.