How to join data from 2 tables based on multiple conditions in SQL

Question

I have two tables as below and am using SQL server

Table1

+-----------------------------------+
|               Codes               |
+-----------------------------------+
| KNC-BAI-PFB-BRAND-ZONE-ADS        |
| KNC-BAI-PFB-CHUCHUANG-BRAND-LINK2 |
| KNC-BAI-PFB-FIXED-PACK1-logo      |
| KNC-GAT-GEN-KWL-CATCHALL          |
| KNC-GAT-GEN-KWL-CATCHALL          |
| KNC-GAT-GEN-KWL-CATCHALL          |
| KNC-XRU-GEN-PFB-Farnell_Exact     |
| KNC-MUK-PFB-FARNELL-exact         |
| KNC-MUK-GEN-SKU-TOP1000           |
+-----------------------------------+



Table2

+-----------------+--------------------------+
|     Level0      |          Level1          |
+-----------------+--------------------------+
| KNC-BAI-PFB     | KNC-BAI-PFB-BRAND-ZONE   |
| KNC-BAI-PFB     | KNC-BAI-PFB-FIXED        |
| KNC-GAT-GEN-KWL | KNC-GAT-GEN-KWL-CATCHALL |
| KNC-XRU-GEN     | KNC-XRU-GEN-PFB          |
| KNC-MUK-PFB     |                          |
| KNC-MUK-GEN-SKU |                          |
+-----------------+--------------------------+

In table2, the values in level1 have additional string added to the level0. If there is no additional string values in level0, they are blank.

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 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, without duplication of the codes.

+-----------------------------------+--------------------------+
|               Codes               |         Newcodes         |
+-----------------------------------+--------------------------+
| KNC-BAI-PFB-BRAND-ZONE-ADS        | KNC-BAI-PFB-BRAND-ZONE   |
| KNC-BAI-PFB-CHUCHUANG-BRAND-LINK2 | KNC-BAI-PFB              |
| KNC-BAI-PFB-FIXED-PACK1-logo      | KNC-BAI-PFB-FIXED        |
| KNC-GAT-GEN-KWL-CATCHALL          | KNC-GAT-GEN-KWL-CATCHALL |
| KNC-GAT-GEN-KWL-CATCHALL          | KNC-GAT-GEN-KWL-CATCHALL |
| KNC-GAT-GEN-KWL-CATCHALL          | KNC-GAT-GEN-KWL-CATCHALL |
| KNC-XRU-GEN-PFB-Farnell_Exact     | KNC-XRU-GEN-PFB          |
| KNC-MUK-PFB-FARNELL-exact         | KNC-MUK-PFB              |
| KNC-MUK-GEN-SKU-TOP1000           | KNC-MUK-GEN-SKU          |
+-----------------------------------+--------------------------+

Answer 1

Just use two left joins on LIKE predicate with table2, selecting distinct values for join on level0. Then select the value from the joins according to priority (from join on level1, if it supplies no rows, then from join on level0). Result here.

with table1 as (
select 'KNC-BAI-PFB-BRAND-ZONE-ADS' as codes
union all select 'KNC-BAI-PFB-CHUCHUANG-BRAND-LINK2'
union all select 'KNC-BAI-PFB-FIXED-PACK1-logo'
union all select 'KNC-GAT-GEN-KWL-CATCHALL'
union all select 'KNC-GAT-GEN-KWL-CATCHALL'
union all select 'KNC-GAT-GEN-KWL-CATCHALL'
union all select 'KNC-XRU-GEN-PFB-Farnell_Exact'
union all select 'KNC-MUK-PFB-FARNELL-exact'
union all select 'KNC-MUK-GEN-SKU-TOP1000'
)
, table2 as (
select 'KNC-BAI-PFB' as level0, 'KNC-BAI-PFB-BRAND-ZONE' as level1
union all select 'KNC-BAI-PFB', 'KNC-BAI-PFB-FIXED'
union all select 'KNC-GAT-GEN-KWL', 'KNC-GAT-GEN-KWL-CATCHALL'
union all select 'KNC-XRU-GEN', 'KNC-XRU-GEN-PFB'
union all select 'KNC-MUK-PFB', ''
union all select 'KNC-MUK-GEN-SKU', ''
)

select
  t1.codes,
  coalesce(t2_1.level1, t2_0.level0) as newcodes
from table1 t1
  left join table2 t2_1
    on t1.codes like t2_1.level1 + '%'
      and t2_1.level1 <> ''
  left join (
    select distinct st2.level0
    from table2 st2
  ) t2_0
    on t1.codes like t2_0.level0 + '%'

Answer 2

I agree with Dai, your table design is really weild, but life is not perfect, we sometimes need to deal with sh*t.

create table table1 (codes varchar(100));
insert into table1 values('KNC-BAI-PFB-BRAND-ZONE-ADS');
insert into table1 values('KNC-BAI-PFB-CHUCHUANG-BRAND-LINK2');
insert into table1 values('KNC-BAI-PFB-FIXED-PACK1-logo');
insert into table1 values('KNC-GAT-GEN-KWL-CATCHALL');
insert into table1 values('KNC-GAT-GEN-KWL-CATCHALL');
insert into table1 values('KNC-GAT-GEN-KWL-CATCHALL');
insert into table1 values('KNC-XRU-GEN-PFB-Farnell_Exact');
insert into table1 values('KNC-MUK-PFB-FARNELL-exact');
insert into table1 values('KNC-MUK-GEN-SKU-TOP1000');

create table table2(level0 varchar(100),level1 varchar(100));
insert into table2 values('KNC-BAI-PFB','KNC-BAI-PFB-BRAND-ZONE');
insert into table2 values('KNC-BAI-PFB','KNC-BAI-PFB-FIXED');
insert into table2 values('KNC-GAT-GEN-KWL','KNC-GAT-GEN-KWL-CATCHALL');
insert into table2 values('KNC-XRU-GEN','KNC-XRU-GEN-PFB');
insert into table2 values('KNC-MUK-PFB',null);
insert into table2 values('KNC-MUK-GEN-SKU',null);

select codes,table2.level1 newcodes from table1 
    left join table2 on table1.codes like  concat(table2.level1 , '%')
    where table2.level1 is not null
union all
select distinct codes,t2.level0 newcodes from table1 
     left join table2 t2 on table1.codes like  concat(t2.level0 , '%')
     where codes not in(
        select codes from table1 
            left join table2 on table1.codes like  concat(table2.level1 , '%')
            where table2.level1 is not null)