Page 1 of 1

Help With Query

Posted: Fri Apr 16, 2010 7:35 am
by dgny06
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

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

Re: Help With Query

Posted: Fri Apr 16, 2010 10:13 am
by infolock
Is this mysql?

If so, try this:

Code: Select all

$sql = "select tbusers.FirstName, tbusers.LastName, DISTINCT(tbcourses.CourseName), MIN(tbrounds.FinalScore) 
           from tbusers 
             INNER join tbrounds on tbusers.UserID = tbrounds.tbusers_UserID
             INNER join tbcourses on tbrounds.tbcourses_CourseID = tbcourses.CourseID
           where tbrounds.ConfirmStatus = 'C'
           group by tbusers.FirstName, tbusers.LastName, tbcourses.CourseName";
Notice that the INNER JOIN syntax changed (when you inner join, specify the parent table that holds the relationship and equate it to the table you are wanting to join with).

Also notice I took out your shorthand table names (c, u, r) and just replaced it with the table name. If you want to do short-hand table defs, you'll want to use the syntax something like : tbrounds as r

Not just tbrounds r

Finally, try querying this statement from a console where you are actually logged into MySQL. If you are getting an error, debug and figure out why/where it's happening.

Hope this helps.