2 table join to return unique records

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 2008 Forums

Transact-SQL (2008)

2 table join to return unique records

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2013-09-05 : 08:43:22

I have 2 tables with the following data:

select Year1, PeriodId from TableA
----------------------
Year1, PeriodId
----------------------
2013, 0
2013, 1
2013, 2
2013, 3
2014, 0
2014, 1
2014, 2
2014, 3
2014, 4

select Actindx, Year1, PeriodId, Balance from TableB
-----------------------------------
Actindx, Year1, PeriodId, Balance
-----------------------------------
1, 2013, 0, 32982
1, 2013, 2, 1524
1, 2013, 3, 2897
1, 2014, 1, 457
1, 2014, 4, 3655

What I'm trying to get as a result set is the following:

-------------------------------------
Actindx, Year1, PeriodId, Balance
-------------------------------------
1, 2013, 0, 32982
1, 2013, 1, 32982
1, 2013, 2, 1524
1, 2013, 3, 2897
1, 2014, 0, 2897
1, 2014, 1, 457
1, 2014, 2, 457
1, 2014, 3, 457
1, 2014, 4, 3655

I tried a left outer join but it seems to only return values where periodid and year1 match.

I know this isn't that complicated, any help is greatly appreciated.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-05 : 08:50:06

You should do something like shown below. I may not have the conditions in the WHERE clause exactly right. If this does not give you the right answers, try adjusting those conditions to meet the criteria that you are looking for:

SELECT b.actindx,
	a.year1,
	a.periodid,
	b.balance
FROM
	TableA a
	OUTER APPLY
	(
		SELECT TOP (1) actindex, Balance
		FROM TableB b
		WHERE b.year1 = a.year1
		AND b.PeriodId <= a.PeriodId
		ORDER BY PeriodId DESC
	) b;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources