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')
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.