Help With Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dgny06
Forum Commoner
Posts: 25
Joined: Thu Jun 14, 2007 11:35 pm

Help With Query

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Re: Help With Query

Post 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.
Post Reply