how to phrase a conditional condition?

Question

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'll have to write the XOR condition as a combination of AND ... OR. SQL Server has no XOR operator. It's also better to put that in the WHERE clause — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 3, 2023 at 12:20
As for SUBSTRING(a.col1, 1, LEN(a.col1) - 2) that's a problem. Indexes only accelerate exact or range searches, not substring searches. This function call will force the server to scan the entire table for matches. What is col1? Does it represent a hierarchy? In that case hierarchyId is the correct type, which does support indexing and parent/child searches. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 3, 2023 at 12:23
Maybe you can use a CTE (common table expression)? Sounds like a good fit for this kind of problem. — Svein Terje Gaup
– Svein Terje Gaup, Commented Jul 3, 2023 at 12:42
I cannot change the typing, you mean by putting the SUBSTRING in WHERE Clause this query performs better? Performance isnt really my issue, I need it just to work. — user22021425
– user22021425, Commented Jul 3, 2023 at 14:42

The Impaler · Accepted Answer · 2023-07-03 14:53:22Z

0

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.

answered Jul 3, 2023 at 14:53

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to phrase a conditional condition?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related