Query Question

[ViperSBT]

OK, I have a table that has multiple entries for a contestant, each entry has the contestants number, speed, and event # where they earned that speed.

 

I want to do a query that shows me the fastest time for each participant and include that date of the tournament by pulling it from the table that is referenced by the event #.  I have this mostly down, but when I do my grouping the event number doesn't come across correctly.  Here is my current query:

SELECT s.participant, MIN(s.time) as time, date_format(t.date, '%m-%d-%y') AS date FROM singles s JOIN tournament t ON s.event = t.enumber GROUP BY s.participant ORDER BY time

This works in that I get all the fields, but the dates do not correlate appropriatly...  If in the table I have something like:

 

Record1 Participant1 Time2 Event1

Record2 Participant1 Time1 Event2

 

I would want to see Time1 as it is the faster of the times and I would want the date for Event 2.  I am getting Time1 but I am getting the date for Event1.

 

What am I doing wrong?

[phoenixx5]

Try adding ASC to the end of your query.  See if this helps

 

SELECT s.participant, MIN(s.time) as time, date_format(t.date, '%m-%d-%y') AS date FROM singles s JOIN tournament t ON s.event = t.enumber GROUP BY s.participant ORDER BY time ASC

[ViperSBT]

Thanks, but that didn't seem to change anything.

[esukf]

Try using a LEFT JOIN

 

SELECT s.participant, MIN(s.time) as time, date_format(t.date, '%m-%d-%y') AS date FROM singles s LEFT JOIN tournament t ON s.event = t.enumber GROUP BY s.participant ORDER BY time

 

 

[ViperSBT]

Nope, I tried it before, but thought I would again and LEFT JOIN doesn't make a difference in this case...