need help with self join query

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

Transact-SQL (2000)

need help with self join query

Author

Topic

ts46235
Starting Member

5 Posts

Posted - 2006-01-23 : 10:15:14

I just can't get it.

I need a list of records that don't have a matching record exactly 1 year later which are joined on 'Number'.

i am also filtering on displaying only the records in the following date range: 7/1/2005 - 6/30/2006 who don't have a matching record 1 year later, so the 7135 record would not show up, even though it doesn't have a matching record a year later (2007-07-01).

id number name Date
2537 DAN1 Dan's Tutoring 2005-07-01
7135 DAN1 Dan's Tutoring 2006-07-01
2112 SS2211 SuperSaver 2005-09-01
3311 Upper3 Upper III 2005-09-07
6336 Lowest LowestClass 2006-08-01

So what should be returned is records 2112 & 3311

This is my failed attempt to do so:

SELECT DISTINCT
a.ID,
a.NUMber,
a.NAME,
a.DaTe

FROM
CLASS a
LEFT OUTER JOIN
CLASS b ON a.NUMber = b.NUMBER
AND a.DATE != DATEADD(YEAR, -1, b.DATE)

I am in great need of this and thanks a lot to any contributors

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-23 : 11:03:56

Filter this however you want:

SELECT	DISTINCT
	a.ID, 
	a.NUMber, 
	a.NAME, 
	a.DaTe 
FROM	CLASS a
	LEFT OUTER JOIN CLASS b ON a.NUMber = b.NUMBER
		AND a.DATE = DATEADD(YEAR, -1, b.DATE)
WHERE	b.NUMBER is null

ts46235
Starting Member

5 Posts

Posted - 2006-01-23 : 14:47:06

that works, thanks a bunch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources