SQL Help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Help

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-03-10 : 22:29:36

I wanted to ask if someone could please help me with this. Thanks.

3 Tables

Tcompanies:
IndexFortune
Ticker

Tdirectors:
DirFName
DirLName
DirAge
IDDir

Tdirectorships:
Ticker
IDDir

SELECT Tcompanies.IndexFortune, Tdirectors.DirFName, Tdirectors.DirLName, Tdirectors.DirAge FROM TDirectors INNER JOIN (TCompanies INNER JOIN TDirectorships ON TCompanies.Ticker = TDirectorships.Ticker) ON TDirectors.IDDir = TDirectorships.IDDir WHERE IndexFortune <= 1000

This produces:

562 Richard A. Abdoo 61
427 Richard A. Abdoo 61
410 Richard A. Abdoo 61
478 John E. Abele 68
694 Jerry E. Abramson 58
321 Alejandro Achaval 72
877 Don E. Ackerman 71
80 F. Duane Ackerman 62
73 F. Duane Ackerman 62
47 F. Duane Ackerman 62

What I want:

410 Richard A. Abdoo 61
478 John E. Abele 68
694 Jerry E. Abramson 58
321 Alejandro Achaval 72
877 Don E. Ackerman 71
47 F. Duane Ackerman 62

(one instance of each director)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 22:53:36

By what criteria do you decide which of these to return?
562 Richard A. Abdoo 61
427 Richard A. Abdoo 61
410 Richard A. Abdoo 61

This will return the Min IndexFortune for each director which seems to be the results from your example


Select	IndexFortune
	,DirFName
	,DirLname
	,DirAge
From	Tdirectors d
JOIN	(
	Select	IDDir, min(IndexFortune) IndexFortune
	From	Tcompanies a
	JOIN	Tdirectorships b 
			ON a.Ticker = b.Ticker
	) c
		ON d.IDDir = c.IDDir

Be One with the Optimizer
TG

evanburen
Posting Yak Master

167 Posts

Posted - 2005-03-10 : 23:01:20

Actually, it doesn't matter which one is displayed as long as there is only one for each director. Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 23:07:43

Sorry, left out the Group by in the derived table:


Select	IndexFortune
	,DirFName
	,DirLname
	,DirAge
From	Tdirectors d
JOIN	(
	Select	IDDir, min(IndexFortune) IndexFortune
	From	Tcompanies a
	JOIN	Tdirectorships b 
			ON a.Ticker = b.Ticker
	Group by IDDir 
	) c
		ON d.IDDir = c.IDDir

Be One with the Optimizer
TG

evanburen
Posting Yak Master

167 Posts

Posted - 2005-03-10 : 23:18:07

Great - I really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources