Multi table Join Angst and Book Recomendation

[spud101]

Ok, boys and girs...easy problem for you, hard problem for me. I need help finding out who has fastest laptime from my racing database for each track. I cannot use a subquery.

Drivers table
driver_id
drivername

LapTimes Table
driver_id
laptime
track

So the info I want to Select is drivername, laptime, track where laptime = min(laptime) for each track

So I'm stuck. I tried something with a temp table but I get lost with joins on joins and aggregates and aliases. Which brings me to my second question on a good SQL book that has good explanation and samples of building advanced queries.

Thanks

 

[BNPMike]

Why can't you select min(laptime) grouped by track?.

 

[spud101]

I'm not sure what you're hinting at.

select min(laptime) , track from laptimes
group by track

returns the quickest time at each track but not the drivername who ran it.

select min(qualifytime) , track, driver_id
from points_db
group by track , driver_id

returns every drivers fastest time at each track, not the fastest driver at each track.
Am I missing some here?

 

[BNPMike]

You're obviously going to join onto drivers which is where you'll get the driver's name. Select driver but don't group by driver.

 

[spud101]

Still don't get it. I don't get the correct information back, without even getting into the joins. I'm familiar with MS SQL aggregates which requires all fields in the group by clause, but I don't know how MSSQL handles it. All I know is the data is incorrect.

Here's a snippet of what I get back

This produces the correct data
QUERY:
select min(laptime), track from laptimes
group by track

RECORDSET:
30.301 Atlanta
15.382 Bristol
41.131 California
30.666 Darlington

If i add driver_id into the mix I get incorrect data back

QUERY:
select min(laptime), track, driver_id from laptimes
group by track

RECORDSET:
30.301 Atlanta 5
15.382 Bristol 5
41.131 California 5
30.666 Darlington 8

The driver_id for the Atlanta race should be 7, Bristol should be 3. etc.
I can confirm this with
SELECT driver_id
FROM laptimes
WHERE laptime = 30.301

 

[r937]

this one's tough, because it is best understood with a subquery:

select track
     , laptime
     , drivername
  from LapTimes t1
inner
  join Drivers
    on t1.driver_id = Drivers.driver_id  
 where laptime =
       ( select min(laptime
           from LapTimes
          where track = t1.track )

i.e. get each row where the laptime is the lowest for all rows for the same track

the above is called a correlated subquery because it uses the correlation variable t1 inside the subquery to refer to the row in the outer main query

as you may know, it is often possible to rewrite a subquery as a join, and this case is one of those instances:

select t1.track
     , t1.laptime
     , drivername
  from LapTimes t1
inner
  join LapTimes t2
    on t1.track = t2.track
   and t1.laptime >= t2.laptime
inner
  join Drivers  
    on t1.driver_id = Drivers.driver_id   
group
    by t1.driver_id
     , t1.track
     , t1.laptime
having count(*) = 1

this is a self-join where each laptime is joined to all other laptimes at the same track which are less than or equal, including itself, and if only one is less than or equal, that must be itself

yes, the self-join method is a bit hard to understand, i admit

also, please note: as written above, the self-join will not return results if there was a tie for fastest lap!!

if you do not get results for a specific track, adjust the HAVING number upwards and run the query for it separately using WHERE track = xxx


rudy

http://r937.com/

SQL Consulting

 

[r937]

book recommendation:

SQL for Smarties

http://amazon.com/exec/obidos/ASIN/1558605762/r937-20

rudy

http://r937.com/

SQL Consulting

 

[spud101]

Yes, that's very good and works.
I'm not going to pretend i understand it. Any recommendations on good books?

Thanks again

Tim

 

[r937]

besides SQL for Smarties?

Sam's SQL Unleashed is okay

rudy

http://r937.com/

SQL Consulting