Every time you think you're getting ahead...

So, yesterday I was talking to one of my coworkers about my SQL class in grad school. I was telling him that it was challenging without be difficult, but that some of the problems on our SQL homework were more complicated than I would have expected. As proof, I sited an one of the early example problems we had in the class. We have a database that lists data from a series of foot races held annually for the past decade or so. Dr. Pardue wanted us to report the year of each race, the full name of the winner, the winner's age, and the average age of everyone racing that year. I've never been a SQL guru, but I'm familiar enough with the language that I can muddle through queries better than most of the folks in my class. Even though it took me almost an hour to get working right (mostly because I wasn't paying attention to my data set, and didn't take advantage of the columns available to me), I was pretty proud of myself for getting it done like I did. Here is the query I wrote.

SELECT
YEAR(tr1.RaceTime) As [Year],
trc1.FirstName + ' ' + trc1.LastName As Racer,
trr1.Time,
trr1.Age,
(
SELECT AVG (CAST (trr2.Age AS Float))
FROM tblRaceResult trr2
WHERE (YEAR(trr2.FinishTime) = YEAR(tr1.RaceTime))
) AS AverageAgeAll
FROM
tblRace tr1
INNER JOIN tblRaceResult trr1 on trr1.RaceID = tr1.RaceID
INNER JOIN tblRacer trc1 on trc1.RacerID = trr1.RacerID
WHERE
trr1.OverAllRank =
( SELECT MIN (trr2.OverAllRank)
FROM tblRaceResult trr2
WHERE (YEAR(trr2.FinishTime) = YEAR(tr1.RaceTime))
)
ORDER BY tr1.RaceTime


Then I showed the problem to my coworker (who is *much* better at this than I am). He took a quick look at the database and in 5 minutes, had a query that output the exact same answer I had, but in less than half the code it took me. Here's his query:

select
YEAR(rac.racetime) as 'Year'
,ISNULL(rcr.FirstName + ' ', '') + ISNULL(rcr.LastName, '') as Racer
,rr.Time
,rr.Age as Age
,(select AVG(CAST(age AS FLOAT)) from tblRaceResult where raceid = rac.raceid )AS 'Average Age'
FROM
tblRace rac
INNER JOIN
tblRaceResult rr ON rac.RaceID = rr.RaceID AND rr.OverAllRank = 1
INNER JOIN tblRacer rcr ON rr.RacerID = rcr.RacerID
ORDER BY 1

I was almost embarrassed that I didn't think of some of the things he did. Here's a few things I learned from reading my coworker's query:

1. I overthought the problem, when I used the year of the race to link the race table with the raceresults table. They have the RaceID as an FKey relationship, I should have just used that and saved myself the trouble.
2. I didn't know you could append conditions to Inner joins. Notice that James didn't need to use a where clause at all, he just included the "AND OverallRank = '1' " to his join statement.
3. I tend to make things harder than they should be. I need to look for the direct links between tables instead of assuming things are harder than they are, and try to outthink my way past a problem.

Anyway, it just goes to show you that no matter how good you think you're getting, there will always be someone better. I'm just really happy that I'm working with people again who I can learn from.

Rich