Inner join with case statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Inner join with case statement?

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-04-26 : 12:04:56

Hi

I was wondering if something like this is possible?

SELECT A.*
FROM GL_ALL_TRANS_2009 A
INNER JOIN #TEMP_1 B
ON A.DATE = B.DATE
AND CASE
WHEN A.CREDIT <> 0 THEN A.CREDIT = B.DEBIT
ELSE A.DEBIT = B.CREDIT
END
AND LEFT(A.ACCOUNT_NUMBER,2) <> LEFT(B.ACCOUNT_NUMBER,2)

Basically I'm trying to say if the credit is <> 0 then join on credit = debit, if not join on debit = credit.

Many thanks!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-26 : 12:09:08

MAybe this???


SELECT
	A.* 
FROM
	GL_ALL_TRANS_2009 A
	INNER JOIN #TEMP_1 B ON
		A.DATE = B.DATE

		AND (
			( A.CREDIT = B.DEBIT AND A.CREDIT <> 0 )
			OR
			( A.DEBIT = B.CREDIT AND A.CREDIT = 0 )
		)

		AND LEFT(A.ACCOUNT_NUMBER,2) <> LEFT(B.ACCOUNT_NUMBER,2)

This join looks pretty weird. You want to join where the account numbers are definitely not the same?


AND LEFT(A.ACCOUNT_NUMBER,2) <> LEFT(B.ACCOUNT_NUMBER,2)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-04-26 : 12:17:15

Great thanks! yep, the logic for joining the account number is correct.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources