Conditional Joins - 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)

Conditional Joins

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-09-03 : 12:10:54

Hi all,

I was wondering if it was possible to make conditional joins using a case statement.

I have imported data into a table that came from an outside source, they have 1 column that combined two of our columns in our warehouse. It is easy enough to determine which rows should match our column, but I am unsure if it can be done in SQL. (I realize I could build two queries and union them, but I wanted a left join to say which items were not matched at all.

Basically...

Table A Column (Input)
Column1
111
222
333
444
N99
N88
N77

Table 2 Columns (Lookup)
Numeric  AlphaNumeric
111      N99  
333      N77

I want a query that does something like...


Select * 
From A 
Left Outer Join B ON
(Case when A.Column1 Like 'N%' Then AlphaNumeric Else Numeric End) = A.Column1

Does this make sense? I want to match to AlphaNumeric when there is a letter, and to numeric when not.

Thanks!

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-09-03 : 13:00:53

Just to give an answer - It can be done, it can lengthen a query by quite some time though, especially if you have 2 large datasets.

Declare  @a table (AID Varchar(4), Name Varchar(10))
Declare  @b table (BID Varchar(4), BID2 Varchar(4))

Insert into @a
Select '1111', 'a' Union
Select '2222', 'b' Union
Select 'A222', 'c' 

Insert into @b
Select '9999', 'A222' Union
Select '1111', 'B233'

Select * 
from @a 
Left Join @b on (Case when Left(AID,1) = 'A' then BID2 Else BID end) = AID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-03 : 13:08:43

I'm not sure if it will perform any better, but you could change the logic around to eleminate the CASE function. I assume it'd still table scan though. So, if you only had a couple different cases a UNION might work better..?

SELECT *
FROM 
	@a AS A
LEFT OUTER JOIN
	@b AS B
	ON 
	(A.AID LIKE 'A%' AND A.AID = B.BID2)
	OR
	(A.AID NOT LIKE 'A%' AND A.AID = B.BID)

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-09-03 : 13:37:34

Y I was thinking of the union, but I wanted to find what did not match a bit easier, so the nulls would be obvious. I suppose I could insert the union into a table and left join it against the original to see what lines did not come back.

I was hoping there was an easier way though.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources