Or in a Join clause - 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 2005 Forums

Transact-SQL (2005)

Or in a Join clause

Author

Topic

Noise
Starting Member

2 Posts

Posted - 2008-04-22 : 17:16:50

I'm joining one table on to another table using one of 2 possibile fields (so table 1 key one can either match table 2 key 1 or key 2)... When the first key is null for a record, the script is to attempt to join using the second key instead. It is possible to have both values present, in which case the first one should be used.

I've taken a few runs at this so far:

...
from table1 t1
left join table2 t2
on
(t1.key1 = t2.key1
or
t1.key1 = t2.key2)

If either t2.key1 or t2.key2 are populated, this works. Unfortunately, it's bringing back multiple records if both key1 and key2 are populated. Question # 1... Is there a different relational operator I can be using instead of OR that would logically look like 'if thie first key didn't find anything try the second instead'?

As an alternative, I've put the NVL to use...

NVL(t2.key1, t2.key2) = t1.key1

That seems to work, but it's pretty heavy on the server. Any suggestions on how else to handle this scenario would be greatly appreciated

(and ya, I didn't design this datamodel).

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-22 : 17:30:15

from table1 t1
left join table2 t2 on t1.key1 = COALESCE(t2.key1, t2.key2)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 17:32:43

from table1 t1
left join table2 t2 on t1.key1 IN (t2.key1, t2.key2)

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 18:42:03

There are probably better ways, but here is a quick and dirty way to do it:

DECLARE @TableA TABLE (ID INT, AVal VARCHAR(10))
DECLARE @TableB TABLE (Key1 INT, Key2 INT, BVal VARCHAR(10))

INSERT @TableA
SELECT 1, 'Foo'
UNION ALL SELECT 2, 'Bar'
UNION ALL SELECT 3, 'Lost'
UNION ALL SELECT 4, 'Yak'

INSERT @TableB
SELECT 1, 3, 'Harpo'
UNION ALL SELECT 99, 3, 'Zeppo'
UNION ALL SELECT 4, 12, 'Groucho'

SELECT
	*
FROM
	@TableA AS A
LEFT OUTER JOIN
	(
		SELECT
			KeyID,
			BVal,
			ROW_NUMBER() OVER(PARTITION BY KeyID ORDER BY KeyID, Part) AS RowNum
		FROM
			(
				SELECT DISTINCT 1 AS Part, Key1 AS KeyID, BVal
				FROM @TableB

				UNION ALL

				SELECT DISTINCT 2 AS Part, Key2 AS KeyID, BVal
				FROM @TableB
			) AS Temp
	) AS B
	ON A.ID = B.KeyID
	AND RowNum = 1

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-22 : 19:50:30

this must be for oracle. i don't think NVL() exists in sql server.

elsasoft.org

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-22 : 21:27:34

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

It is very rare that joining using an OR clause is a good idea or necessary. Most likely it is not doing what you think it is but resulting in duplicates. Simply use multiple outer joins, as described in the link above.

Thus:

select ...
from table1 t1
left join table2 t2a on t1.key1 = t2a.key1
left join table2 t2b on t1.key1 = t2b.key2

And in your SELECT, use CASE or ISNULL or COALESCE to grab columns from either t2a or tb2 as necessary.

- Jeff
http://weblogs.sqlteam.com/JeffS

Noise
Starting Member

2 Posts

Posted - 2008-04-23 : 16:36:10

You are right jezemine, this is Oracle in this case (which is messing me up trying to write this ^^)

"It is very rare that joining using an OR clause is a good idea or necessary."

I agree, but it's seeming nessacary here (the example was pretty simplified... It's actually joining a 3 part key obtained from a subquery across 6 tables being joined to another database on 1 of 2 keys found spread between 3 tables... Integrating data that just doesn't align well). The problem with using left joins is the key2 field is often populated with junk values, including alot of duplicates... So a second left join will return additional rows I don't want. Though that article you linked was really helpful.

But I agree that this OR in a join clause is going to be troublesome... So for a solution, I've created a view that uses the COALESCE(t2.key1, t2.key2) function in it's select statement and narrows this 2 part key down to one field in advance. Eliminates the need for an OR in a join clause and I'm only joining on one field once again. More than one way to skin a cat I guess

Thanks for all the input!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources