[SOLVED] INNER JOIN help needed

[ViperSBT]

OK, here is the query I am trying to execute:

SELECT b.breed AS Breed, d.rname AS RegisteredName, s.time AS Time, CONCAT(h.fname, ' ', h.lname) AS Owner, h.street1 AS Street, h.city AS City , s1.pc AS State, h .zip AS ZipCode, c.cname AS Club
FROM singles s
INNER JOIN(SELECT b1.breed, MIN(s1.time) AS besttime FROM singles s1 JOIN dogs d1 ON s1.dog = d1.dnumber JOIN breeds b1 ON d1.breed = b1.id GROUP BY b1.breed) AS fastest ON s.time = fastest.besttime
JOIN dogs d ON s.dog = d.dnumber
JOIN breeds b ON d.breed = b.id
JOIN handler h ON d.owner = h.hnumber
JOIN tournament t ON s.event = t.enumber
JOIN state s1 ON h.state = s1.id
JOIN clubs c ON c.cnumber = d.club
WHERE t.date LIKE '2007%'
ORDER BY b.breed, s.time ASC

What I am trying to do is to identify the fastest dog (by time) for each breed of dog in that has competed.  This current query gives me each of the dog's fastest time for each of the breeds that has competed.  I only want the fastest one for each breed.  What am I missing?

[fenway]

Forgot about all those other joins for a minute.... including the ones in your dervied table.  You already have the min/group by for the singles by breed; then just join this table to singles table using breedid and besttime, and THEN join the rest in.

[ViperSBT]

Thanks...  It took me a bit to understand what you were trying to tell me, but I got it figured out.

 

THANK YOU!!!