Getting only the first related result (with complications)
-
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.
-
Are games always related in groups? In your test data, games 1, 2 and 3 are a group of related games. Similarly for games 6 and 7.
-
No, they're not always related. And even if they are related, there might not be any relationship data available for them.
David.
-
It's a complicated world...
Try this:
declare @IDs table(Id int)
set rowcount 1
-- execute the same SQL statement 3 times
insert @IDs select id from Games
where id not in
(
select r.rID from @IDs i inner join Related r on i.ID = r.ID
union select id from @IDs
)
order by popularity desc
insert @IDs select id from Games
where id not in
(
select r.rID from @IDs i inner join Related r on i.ID = r.ID
union select id from @IDs
)
order by popularity desc
insert @IDs select id from Games
where id not in
(
select r.rID from @IDs i inner join Related r on i.ID = r.ID
union select id from @IDs
)
order by popularity desc
set rowcount 0
select g.* from Games g inner join @IDs i on g.ID = i.ID
-
CREATE TABLE #temp (ID2 int IDENTITY(1,1),[ID] int,Title varchar(30),Popularity int,Related int)
INSERT INTO #temp ([ID],Title,Popularity,Related)
SELECT g.ID,g.Title,g.Popularity,MAX(ISNULL(SIGN(r.rID),0))
FROM Games g
LEFT OUTER JOIN Related r ON r.ID=g.ID
GROUP BY g.ID,g.Title,g.Popularity
ORDER BY g.Popularity DESC
SET ROWCOUNT 3
SELECT t.[ID],t.Title,t.Popularity
FROM #temp t
WHERE NOT EXISTS(SELECT * FROM #temp t2 WHERE t2.ID2 < t.ID2 AND t2.Related > 0 AND t.Related > 0)
ORDER BY t.ID2
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
I think we read the following differently:
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?
My interpretation was that you cannot select a game that is related to a game that you have already selected. Your interpretation, David B, (correct me if I am wrong
) is that you cannot select a game that is related to another game (no matter which one), except for the first one.I think we need David M to tell which interpretation is correct. Probably David B's, his ouija board is almost always right
-
Yes could be two interpretations
Based on your interpretation
SELECT TOP 3 g.[ID],g.Title,g.Popularity
FROM Games g
WHERE NOT EXISTS(SELECT * FROM Games g2
INNER JOIN Related r2
ON r2.[ID] = g2.[ID] AND r2.[rID] = g.[ID]
WHERE g2.[ID] < g.[ID])
ORDER BY g.[ID] ASC
This assumes the Games table is ordered Popularity DESC with sequential asc ID (as per first post). If the true data in the Games table is not like that then an intermediate temp table will be needed to get sequential ID's.
Far away is close at hand in the images of elsewhere.
Anon. -
Very nice and certainly better than my suggestion
-
Thanks for all the suggestions. I am actually looking to include related results but only one result from the related, not both. So I am not looking to exclude the related results entirely.
I haven't had a chance to work with your code samples (this being a nights and weekends project) but will hopefully have some time to do so this Sat. and will let you know how it works. The website it is for is GameSecretary.Com in case you want to see it. Specifically for the Game Generator section (nobody wants to play Tag Dodgeball and Dr. Dodgeball - you want something like Tag Dodgeball and Basketball).
David.
-
I realised that there may be a problem with David B's second suggestion. Consider the example below. David B's suggestion returns 1, 4, 5. If my interpretation is correct I think it should be 1, 3, 4. But I don't know if it is possible that 1 is related to 2 and 2 is related to 3, without having 1 related to 3.
I would like to say a little about math. Please jump directly to the code below if you don't feel like reading it...
David M, you should probably ask yourself if the following is true:
If a is related to b then b is related to a.
If a is related to b and b is related to c then a is related to c.
Together with the (rather trivial) condition that a is always related to a, these conditions define what mathematicians call an equivalence relation. Such a relation gives rise to a partition of the objects involved. In your original data set, this would be (1,2,3), (4), (5), (6,7).
Here's the code
CREATE TABLE Games
(ID int,
Title varchar(30),
Popularity int)
go
CREATE TABLE Related
(ID int,
rID int
)
go
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)
INSERT INTO Related (ID, rID) VALUES (1,2)
INSERT INTO Related (ID, rID) VALUES (2,1)
INSERT INTO Related (ID, rID) VALUES (2,3)
INSERT INTO Related (ID, rID) VALUES (3,2)
INSERT INTO Related (ID, rID) VALUES (6,7)
INSERT INTO Related (ID, rID) VALUES (7,6)
-
"related" is confusing.
INSERT INTO Related (ID, rID) VALUES (1,2)
INSERT INTO Related (ID, rID) VALUES (2,1)
can tagdodgeball be related to freeball, but freeball not related to tagdodgeball?
can tagdodgeball be related to freeball, and freeball related to kickball, but kickball not related to tagdodgeball?
how are you going to enforce the rules? how do you want your counts presented?
-
If a is related to b, then b will be related to a.
Strictly speaking, if a is related to b and b to c, a is not necessarily related to c. However, the number of times in which a=b=c is not true is very minimal.
David.
-
David's code looks like it will work great. My one problem is that, in fact, the larger data set is not ordered ASC by ID and DESC by Popularity, rather while it is ASC by ID, it is random by Popularity. Can you help me with the intermediary temp table I will need?
David.
-
Okay. I think I got it. Here is what the code actually looks like (its a bit more complex than the sample data I originally gave)
If @varAI >0
BEGIN
SET ROWCOUNT @varAI
Insert into #Results
select * FROM Game G
LEFT OUTER JOIN #templastplayed LP
On G.ID=LP.gID2
LEFT JOIN #temppopularity GP
On G.ID=GP.gID
WHERE G.Type=1
AND G.Approved=1
AND (LP.gID2 IS NULL
OR ((LP.LastPlayedDate gp.Popularity2
OR (gp2.Popularity = gp.Popularity2 and g2.ID < g.ID)))
ORDER BY gp.Popularity2 DESC
END
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply