Help With Query
Posted: Fri Apr 16, 2010 7:35 am
As always, thanks in advance for any help!
I have a site that tracks golf scores. I have a table that stores the users, one for the courses and one for the actual rounds of golf. I am trying to write a query that will return all of the distinct courses, what the lowest score was for each, and who shot that score where the status is "C" for confirmed. I thought the query below would work but it didn't, and I am thinking that I may need a sub-query or something. Please help! I think the query below should give you enough information about my tables, but if you need more just let me know.
Thanks,
Devin
I have a site that tracks golf scores. I have a table that stores the users, one for the courses and one for the actual rounds of golf. I am trying to write a query that will return all of the distinct courses, what the lowest score was for each, and who shot that score where the status is "C" for confirmed. I thought the query below would work but it didn't, and I am thinking that I may need a sub-query or something. Please help! I think the query below should give you enough information about my tables, but if you need more just let me know.
Thanks,
Devin
Code: Select all
select
u.FirstName,
u.LastName,
DISTINCT(c.CourseName),
MIN(r.FinalScore)
from tbusers u
join tbrounds r on r.tbusers_UserID = u.UserID
join tbcourses c on c.CourseID = r.tbcourses_CourseID
where r.ConfirmStatus = 'C'
group by u.FirstName, u.LastName, c.CourseName