IF test in INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IF test in INNER JOIN

Author

Topic

markusf
Starting Member

15 Posts

Posted - 2006-09-22 : 10:07:20

Hello All :)

I want to join 2 tables based on EITHER one field (f1), but if this field contains an empty string, i want to use another field (f2)

This is an outline of what i want to do:
SELECT *
FROM t1
INNER JOIN t2 ON (t2.ID = [IF t1.f1 <> '' t1.f1 ELSE t1.f2 ])

--
Markus Foss, Norway

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-22 : 10:13:05

[code]SELECT *
FROM t1
INNER JOIN t2
ON 1 = (case
When t1.f1 <> '' then Case
When t1.f1 = t2.id then 1
else 0
end
else
case
when t1.f2 = t2.id then 1
else 0
end
end)[/code]

Harsh Athalye
India.
"Nothing is Impossible"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-22 : 10:30:48

It is just about always better to do TWO outer joins, one for each condition, rather than putting a CASE or an "OR" clause in your join expression. Often, things don't work out the way you expect when you put conditionals on a join.

So, in your case:

SELECT *
FROM t1
LEFT OUTER JOIN t2 t2a on t2a.id = t1.f1
LEFT OUTER JOIN t2 t2b on t2b.id = t1.f2

Notice that t2 is joined twice, aliased as t2A and t2b, and on the two different possibilities. From there, using a CASE expression, you can decide which value to return from which alias of t2. For example, if you want to return "Name" from t2, you'd do it something like this:

SELECT t1.*, CASE WHEN t2a.id Is Null THEN t2b.Name ELSE t2a.Name END as Name
FROM t1
LEFT OUTER JOIN t2 t2a on t2a.id = t1.f1
LEFT OUTER JOIN t2 t2b on t2b.id = t1.f2

That is just an example.

Note: If you need the JOIN to t2 to restrict or filter your result set, then you'll need some criteria since the OUTER JOINS won't do that:

SELECT ...
FROM t1
LEFT OUTER JOIN t2 t2a on t2a.id = t1.f1
LEFT OUTER JOIN t2 t2b on t2b.id = t1.f2
WHERE t2a.ID is not null OR t2b.ID is not null

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources