Query 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)

Query Help

Author

Topic

bobbabuoy
Starting Member

33 Posts

Posted - 2006-09-06 : 18:15:52

The following query returns 1242645 records when it should return 555:


SELECT r.FirstName, r.LastName, t.TeamName, g.Grade06, r.Gender, rs.RaceName, ir.Bib 
FROM Roster r INNER JOIN Grades g ON r.RosterID = r.RosterID 
INNER JOIN Teams t ON r.TeamsID = t.TeamsID 
INNER JOIN IndRslts ir ON ir.RosterID = r.RosterID 
INNER JOIN Races rs ON rs.MeetsID = ir.MeetsID 
WHERE ir.MeetsID = 13 AND rs.RacesID = ir.RacesID AND ir.Bib <> 0  
ORDER BY ir.Bib

Can someone tell me what is wrong with it? I am sure there is something I don't know about joins that will jump out at someone who knows more than me.

Thanks in advance~

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-09-06 : 18:19:53

Just a guess but should r.RosterID = r.RosterID be r.RosterID = g.RosterID

SELECT r.FirstName, r.LastName, t.TeamName, g.Grade06, r.Gender, rs.RaceName, ir.Bib
FROM Roster r INNER JOIN Grades g ON r.RosterID = g.RosterID
INNER JOIN Teams t ON r.TeamsID = t.TeamsID
INNER JOIN IndRslts ir ON ir.RosterID = g.RosterID
INNER JOIN Races rs ON rs.MeetsID = ir.MeetsID
WHERE ir.MeetsID = 13 AND rs.RacesID = ir.RacesID AND ir.Bib <> 0
ORDER BY ir.Bib

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking