sql-serverconditional-statementsself-join

how to phrase a conditional condition?


I want to query a self join that will only join on the first condition and only if it cant find a match it will join the second condition

I tried this syntax which doesnt work for me, because it will understandbly always join both conditions

SELECT a.id, a.id2, a.col1, b.id2 from test a
LEFT JOIN test b ON a.id = b.id AND 
(SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1 OR b.col1 = '00')

What I need is like an XOR instead.

Example: table

id id2 col1
4080 2147 00
4080 2148 0001
4080 2149 0002
4080 2150 000201
3820 2069 00
3820 2070 000101

desired result

a.id a.id2 a.col1 b.id2
4080 2147 00 NULL
4080 2148 0001 2147
4080 2149 0002 2147
4080 2150 000201 2149
3820 2069 00 NULL
3820 2070 000101 2069

Data

CREATE TABLE [test](
[id] [int] NOT NULL,
[id2] [int] NOT NULL,
[col1] [varchar](6) NOT NULL
)
INSERT INTO [test] VALUES
(4080,2147,'00'),
(4080,2148,'0001'),
(4080,2149,'0002'),
(4080,2150,'000201'),
(3820,2069,'00'),
(3820,2070,'000101')

Solution

  • You can do two separate left joins and then pick the "best match" as needed.

    For example:

    select a.*, 
      case when a.col1 = '00' then null else coalesce(b.id2, c.id2) end as bid2
    from test a
    left join test b on b.id = a.id 
      and SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1
    left join test c on c.id = a.id and c.col1 = '00'
    

    Result:

     id    id2   col1    bid2 
     ----- ----- ------- ---- 
     4080  2147  00      null 
     4080  2148  0001    2147 
     4080  2149  0002    2147 
     4080  2150  000201  2149 
     3820  2069  00      null 
     3820  2070  000101  2069 
    

    See running example at db<>fiddle.