How to join data from 2 tables based on multiple conditions?

Question

I am new to using SQL and reaching out to the community for help on a project that I am working on and currently facing problem.

I have two tables as below.

Table1

+----------------------------------------------------------+
| Codes |
+----------------------------------------------------------+
| KNC-GUSA-GEN-KWL-DSA-RLSA-PURCHASED-1234 |
| KNC-GUSA-GEN-KWL-DSA-RLSA-4563456 |
| KNC-GUSA-GEN-KWL-DSA |
| KNC-GUK-GEN-KWL-RLSA-PUR-2134234 |
| KNC-GUK-GEN-KWL-RLSA-PUR-12343 |
| KNC-GUK-GEN-KWL-RLSA-PUR |
+----------------------------------------------------------+

Table2
+--------------------------------+------------------------------------------+
| Level0 | Level1 |
+---------------------------------+-----------------------------------------+
| KNC-GUK-GEN-KWL | KNC-GUK-GEN-KWL-RLSA |
| KNC-GUSA-GEN-KWL-DSA | KNC-GUSA-GEN-KWL-DSA-RLSA |
+---------------------------------+-----------------------------------------+
In table2, the values in level1 have an additional string added to the level0.

I would want to get a new column with the data from table1 based on the condition using table2.

I am trying to get output something like this.

if(table1.codes contains table2.level1) give me value from table2.level1 else give me the value from table2.level0 as "newcode"

I used the join condition and getting as output since the codes are being matched to both the columns in table2 and the values are repeated.

Output that I am getting - Table3

+---------------------------------------------+--------------------------------+
| Codes | Newcode |
+------------------------------------------- --+--------------------------------+
| KNC-GUK-GEN-KWL-RLSA-PUR-2134234 | KNC-GUK-GEN-KWL |
| KNC-GUK-GEN-KWL-RLSA-PUR-2134234 | KNC-GUK-GEN-KWL-RLSA |
| KNC-GUK-GEN-KWL-RLSA-PUR | KNC-GUK-GEN-KWL |
| KNC-GUK-GEN-KWL-RLSA-PUR | KNC-GUK-GEN-KWL-RLSA |
+----------------------------------------------+---------------------------------+
I would want to get the output as below, where if there is a value found in level1 that value is returned and then if there is not match in level1, match it with level0 and return level0 value against the code. it shouldn't match both the values in table2

+-----------------------------------------------+-------------------------------+
| Codes | Newcode |
+-----------------------------------------------+-------------------------------+
| KNC-GUK-GEN-KWL-RLSA-PUR-2134234 | KNC-GUK-GEN-KWL-RLSA |
| KNC-GUK-GEN-KWL-RLSA-PUR | KNC-GUK-GEN-KWL-RLSA |
+-----------------------------------------------+-------------------------------+

Answer 1

Hi @Harini ,

Thank you so much for posting here.

You could refer below and check whether it is working. Thanks.

drop table if exists Table1  
drop table if exists Table2  
drop table if exists Table3  
  
create table Table1  
(  
Codes varchar(100)  
)  
  
insert into Table1 values  
('KNC-GUSA-GEN-KWL-DSA-RLSA-PURCHASED-1234'),  
('KNC-GUSA-GEN-KWL-DSA-RLSA-4563456'),  
('KNC-GUSA-GEN-KWL-DSA'),  
('KNC-GUK-GEN-KWL-RLSA-PUR-2134234'),  
('KNC-GUK-GEN-KWL-RLSA-PR-12343'),  
('KNC-GUK-GEN-KWL-RLSA-PUR')  
  
create table Table2  
(  
Level0 varchar(100),  
Level1 varchar(100)  
)  
  
insert into Table2 values  
('KNC-GUK-GEN-KWL','KNC-GUK-GEN-KWL-RLSA'),  
('KNC-GUSA-GEN-KWL-DSA','KNC-GUSA-GEN-KWL-DSA-RLSA')  
  
create table Table3  
(  
Codes varchar(100),  
Newcode varchar(100)  
)  
  
insert into Table3  
select a.Codes,   
case when a.Codes like b.Level1+'%' then b.Level1   
     when a.Codes like b.Level0+'%' and a.Codes not like b.Level1+'%' then b.Level0   
end NewCode   
from Table1 a, Table2 b   
where a.Codes like b.Level0+'%'  
  
select * from Table3  

Output:

Codes	Newcode  
KNC-GUK-GEN-KWL-RLSA-PUR-2134234	KNC-GUK-GEN-KWL-RLSA  
KNC-GUK-GEN-KWL-RLSA-PR-12343	KNC-GUK-GEN-KWL-RLSA  
KNC-GUK-GEN-KWL-RLSA-PUR	KNC-GUK-GEN-KWL-RLSA  
KNC-GUSA-GEN-KWL-DSA-RLSA-PURCHASED-1234	KNC-GUSA-GEN-KWL-DSA-RLSA  
KNC-GUSA-GEN-KWL-DSA-RLSA-4563456	KNC-GUSA-GEN-KWL-DSA-RLSA  
KNC-GUSA-GEN-KWL-DSA	KNC-GUSA-GEN-KWL-DSA  

Best regards
Melissa

---

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

It is not clear why your sample output contains two rows only; maybe there is an additional rule.

Try this query too:

select *,
    coalesce(
        (select top(1) Level1 from Table2 where t1.Codes = Level1 or t1.Codes like Level1 + '-%' order by len(Level1) desc ),
        (select top(1) Level0 from Table2 where t1.Codes = Level0 or t1.Codes like Level0 + '-%' order by len(Level0) desc )
    ) as Newcode
from Table1 as t1

It assumes that values do not contain '%', '_', '['.

Answer 3

DECLARE @Table1 TABLE (
    Codes varchar(50)
);
INSERT INTO @Table1 VALUES
('KNC-GUSA-GEN-KWL-DSA-RLSA-PURCHASED-1234'),
('KNC-GUSA-GEN-KWL-DSA-RLSA-4563456'),
('KNC-GUSA-GEN-KWL-DSA'),
('KNC-GUK-GEN-KWL-RLSA-PUR-2134234'),
('KNC-GUK-GEN-KWL-RLSA-PR-12343'),
('KNC-GUK-GEN-KWL-RLSA-PUR');

DECLARE @Table2 TABLE (
    Level0 varchar(50),
    Level1 varchar(50)
);

INSERT INTO @Table2 VALUES
('KNC-GUK-GEN-KWL','KNC-GUK-GEN-KWL-RLSA'),
('KNC-GUSA-GEN-KWL-DSA','KNC-GUSA-GEN-KWL-DSA-RLSA');

;WITH CTE_ExtraString AS (
    SELECT Level0, Level1, SUBSTRING(Level1, LEN(Level0 + '-') + 1, LEN(Level1) - LEN(Level0 + '-')) AS ExtraString 
    FROM @Table2
)

SELECT t1.Codes, CASE WHEN CHARINDEX(t2.ExtraString, t1.Codes) > 0 THEN t2.Level1 ELSE t2.Level0 END AS New_Code
FROM @Table1 AS t1
LEFT JOIN CTE_ExtraString AS t2 ON CHARINDEX(t2.Level0, t1.Codes) > 0;