vendredi 24 juin 2016

How do I perform this php loop and multiple mysql queries with just one query?

I have a query which gets the earliest date in which race times start being recorded for runners in a race:

SELECT
    MIN(races.eventdate)
FROM
    races,
    runners
WHERE
    races.id=runners.raceid AND runners.time_hsecs IS NOT NULL

races table has a list of races, as well as the tracks they are at. runnerstable has the runners in each race and their times.

The query above gets the earliest date in which finish times were being recorded. But what if I wanted to get the earliest date for each trackname? With my limited experience, I would create a loop that goes through each track names and then perform the same query above but isolated just to each track, for example (pseudo code)

foreach($trackname_array as $some_name) {

    SELECT
        MIN(races.eventdate)
    FROM
        races,
        runners
    WHERE
        races.id=runners.raceid AND runners.time_hsecs IS NOT NULL
    AND
        races.trackname=$some_name

    // returns earliest eventdate that matches the query for trackname

}

But I would like to learn how is something like this done purely in sql?

Aucun commentaire:

Enregistrer un commentaire