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

INNER JOIN

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-28 : 12:58:24

I HAVE TABLE "A" AND TABLE "B" WHERE THE NUMBER AND THE DATE ARE THE KEY. IN TABLE "B" THERE IS ANOTHER FIELD AMOUNT.

I NEED TO DO AN INNER JOIN OF TABLE "A" AND TABLE "B" IN SUCH A WAY THAT I GET THE NOT THE MOST RECENT RECORD OF TABLE "B" BUT RATHER ONE RECORD BEFORE THE MOST RECENT RECORD IN TABLE "B" THIS IS BECAUSE WHEN RECORDS ARE BEING DELETED IN THE APPLICATION THE SYSTEM CREATES AN ADDITIONAL RECORD IN TABLE 'B' WHERE THE AMOUNT IS ZERO AND I NEED TO PRINT AN AMOUNT WHICH IS IN THE RECORD PRIOR TO THE LAST RECORD.

TABLE A

1234 1/1/12
1234 5/2/12

TABLE b

1234 1/1/12 500.00
1234 5/2/12 0.00

I NEED TO GET THE INFO 500.00 FROM TABLE "B"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-28 : 13:06:47

;with cte as
(
select *, seq = row_number() over (partition by id order by dte desc) from tblb
)
select *
from tbla a
join cte b
on a.id = b.id
and a.dte = b.dte
and b.seq = 2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-28 : 14:24:48

THIS SEEMS TO BE NOT WORKING... IS THERE A WAY TO SELECT THE ONE TO THE LAST RECORD IN A SINGLE TABLE.. WHERE THE KEY CAN BE EITHER THE NUMBER OR DATE OR BOTH???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-28 : 22:35:08

[code]
select *
from
(
select *,row_number() over (partition by id order by dte desc) as seq
from tbla a
join cte b
on a.id = b.id
and a.dte = b.dte
and b.amount >0
)t
where seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources