Getting only the first related result.

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)

Getting only the first related result.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2006-01-30 : 21:20:20

Here is the first table:
CREATE TABLE "Games"
("ID" int,
"Title" varchar(30),
"Popularity" int)
Here is the second table:
CREATE TABLE "Related"
("ID" int,
"rID" int)
Here is the data for the first table:
INSERT INTO Games (id, title, popularity) VALUES (1, 'Tag Dodgeball', 10)
INSERT INTO Games (id, title, popularity) VALUES (2, 'Freeball', 10)
INSERT INTO Games (id, title, popularity) VALUES (3, 'Doctor Dodgeball', 10)
INSERT INTO Games (id, title, popularity) VALUES (4, 'Kickball', 8)
INSERT INTO Games (id, title, popularity) VALUES (5, 'Fooseball', 8)
INSERT INTO Games (id, title, popularity) VALUES (6, 'Basketball', 7)
INSERT INTO Games (id, title, popularity) VALUES (7, 'Knockout', 6)
Here is the data for the second table:
INSERT INTO Related (ID, rID) VALUES (1,2)
INSERT INTO Related (ID, rID) VALUES (2,1)
INSERT INTO Related (ID, rID) VALUES (1,3)
INSERT INTO Related (ID, rID) VALUES (2,3)
INSERT INTO Related (ID, rID) VALUES (3,1)
INSERT INTO Related (ID, rID) VALUES (3,2)
INSERT INTO Related (ID, rID) VALUES (6,7)
INSERT INTO Related (ID, rID) VALUES (7,6)
Now, lets say a person wants to grab the top three results:
SET ROWCOUNT 3
Select * from Game ORDER BY Popularity
Result Set:
1 Tag Dodgeball 10
2 Freeball 10
3 Dr. Dodgeball 10
But, what if this person doesn't want to play three related games in a row? This is my dilemma. How do I get the following result set:
1 Tag Dodgeball 10
2 Kickball 8
3 Fooseball 8
The related table tells which games are related to which, but how do I get the select statement to realize that it should not select any games that are related once it already has one in the result set?
I can't use distinct, b/c if I do, any games which don't have a relationship are eliminated, as their rID is null.
Pseudo-Code might look something like this:
SET ROWCOUNT 3
Select * from Game ORDER BY Popularity (where ID !=rID)
Please help. I've banging my head against this one for quite some time.
Respectfully,
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 01:25:11

select id, title, popularity from Games G
where title=(select top 1 title from Games where popularity =G.popularity)

Madhivanan

Failing to plan is Planning to fail

davidshq
Posting Yak Master

119 Posts

Posted - 2006-01-31 : 14:08:18

That gives me:
1 Tag Dodgeball 10
But what if I want the result set I gave in my first post?
1 Tag Dodgeball 10
2 Kickball 8
3 Fooseball 8
It needs to select the Top 3, but also has to only select one from each related.
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 09:58:06

If you meant Trees and Hierarchies, refer this
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

davidshq
Posting Yak Master

119 Posts

Posted - 2006-02-04 : 00:17:14

I've received some helpful suggestions in this thread, in case anyone wants to see them: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=254838&p=1.
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources