reversing 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

General SQL Server Forums

New to SQL Server Programming

reversing inner join

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-07-20 : 10:00:08

hi i need to reverse this sql.


FROM (SELECT	ld.BankID		,
				ld.BankNumber	,
				ld.CurrencyID	,
				ld.BankDate		,
				(SELECT TOP 1 lb.Balance
					FROM	Harvest.dbo.BankStatement lb
					WHERE	lb.BankID		=	ld.BankID
					AND		lb.BankNumber	=	ld.BankNumber
					AND		lb.CurrencyID	=	ld.CurrencyID
					AND		lb.BankDate		=	ld.BankDate
					ORDER BY lb.Sequence DESC)
				As CIMABalance	
		FROM (SELECT	bs.BankID		,
						bs.BankNumber	,
						bs.CurrencyID	,
						MAX(bs.BankDate)
						AS BankDate		
				FROM	Harvest.dbo.BankStatement bs
				GROUP BY bs.BankID, bs.BankNumber, bs.CurrencyID ) ld ) ccb
INNER
JOIN	[HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
ON		mcb.Account_No	=	ccb.BankNumber
AND		mcb.Currency	=	ccb.CurrencyID

i need to start with
[HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb

and inner join with the first bit anyone have any ideas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 10:01:44

don't quite understand what do you want here and what do you mean by reverse

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-07-20 : 10:05:06

in that example im going from one database to another.
i need to go the other way around. i cant really explain it any better im afraid.

i need to start with
[HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
and inner join it to


FROM (SELECT	ld.BankID		,
				ld.BankNumber	,
				ld.CurrencyID	,
				ld.BankDate		,
				(SELECT TOP 1 lb.Balance
					FROM	Harvest.dbo.BankStatement lb
					WHERE	lb.BankID		=	ld.BankID
					AND		lb.BankNumber	=	ld.BankNumber
					AND		lb.CurrencyID	=	ld.CurrencyID
					AND		lb.BankDate		=	ld.BankDate
					ORDER BY lb.Sequence DESC)
				As CIMABalance	
		FROM (SELECT	bs.BankID		,
						bs.BankNumber	,
						bs.CurrencyID	,
						MAX(bs.BankDate)
						AS BankDate		
				FROM	Harvest.dbo.BankStatement bs
				GROUP BY bs.BankID, bs.BankNumber, bs.CurrencyID )

instaed of the way it is

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-07-20 : 10:30:02

im trying to find a list of banks on maes that are not in the other database
so i have to start with this part
[HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
and inner join it to the other table in the other database

does that make more sense

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 10:33:47

this should help http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-07-20 : 10:58:13

its not really what im looking for. but thanks

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-07-20 : 11:28:07

is this anyway right


SELECT  cb.Account_No	,
		cb.Currency		,
		cb.Balance		,
		bs.BankNumber	,
		bs.currencyID	,
		bs.Balance		
		
FROM [HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances cb
INNER
JOIN Harvest.dbo.BankStatement bs
ON cb.Account_No = bs.BankNumber
AND	cb.Currency	=	bs.CurrencyID
and bs.BankNumber is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources