Search code examples
mysqlsqlgroup-byinner-join

mySQL group by function showing lack of data

What I'm after is to see what is the fastest lap time for particular races, which will be identified by using race name and race date. 

SELECT lapName AS Name, lapDate AS Date, T
FROM Lap 
    INNER JOIN Race ON Lap.lapName = Race.Name
        AND Lap.lapDate = Race.Date
GROUP BY Date;

It currently only displays 3 different race names, with 4 different dates, meaning I've got 4 combinations total, when there are in fact 9 unique race name, race date combinations.

Unique race data is stored in the Race table. Laptimes are stored in the LapInfo table.

I'm also getting a warning about my group statement saying it is ambiguous though it still runs.

Solution

  • You don't seem to need a join for this:

    SELECT l.lapRaceName, l.lapRaceDate, 
       MIN(l.lapTime)
FROM LapInfo l
GROUP BY l.lapRaceName, l.lapRaceDate;

    If you don't need a JOIN, it is superfluous to put one in the query.