I have 3 tables:

1. event_timestamps with colums Race_number, timestamp
2. event_entry with Race_number, User_id
3. user with user_id, Firstname, lastname

I want to find race_numbers that are not linked to a user_id, and count laps while I'm at it by Joining event_timestamps and event_entry.

select event_entry.user_id, max(timestamp), event_timestamps.race_number, count(event_timestamps.race_number) 
from event_timestamps
left join event_entry on event_timestamps.race_number = event_entry.race_number and event_entry.event_id=430 
where timestamp > '2022-05-28 11:50:00' and timestamp < '2022-05-29'
group by event_timestamps.race_number 
order by count(event_timestamps.race_number) desc , max(timestamp);

Output

But when I join tbl.user the Null value disappears. I want to display the NULL so I can see if we are missing user data. This query sort of works but the NULL value is not displaying:

select user.firstname, user.lastname, max(timestamp), event_timestamps.race_number, count(event_timestamps.race_number)
from event_timestamps
left join event_entry on event_timestamps.race_number = event_entry.race_number
inner join user on user.user_id = event_entry.user_id
where timestamp > '2022-05-28 11:50:00' and timestamp < '2022-05-29' and event_entry.event_id=430
group by event_timestamps.race_number
order by count(event_timestamps.race_number) desc , max(timestamp);```

So I want race_number 1000 (for example) to display as well with NULL values in firstname, lastname. Any assistance would be much appreciated because this is breaking my novice brain!

The events_timestamps has multiple occurances of the same race_number as the user completes laps. We count the laps and creat rank by using the last lap time (MAX timestamp) and sorting from there.

CodePudding user response：

Try it like this:

SELECT user.firstname, user.lastname, s.timestamp, s.race_number, s.user_id, s.count 
FROM (
    SELECT event_entry.user_id as user_id, max(timestamp) as timestamp, event_timestamps.race_number as race_number, count(event_timestamps.race_number) as count 
    from event_timestamps
    Left join event_entry on event_timestamps.race_number = event_entry.race_number and event_entry.event_id=430 
    Where timestamp > '2022-05-28 11:50:00' and timestamp < '2022-05-29'
    group by event_timestamps.race_number, event_entry.user_id
) s
LEFT JOIN user on user.user_id = s.user_id
order by s.count desc , s.timestamp

like @Marshal_c said, inner join doesn't work, cause it gets rid of NULLs, viz. SQL JOIN and different types of JOINs

also like @Honk_der_Hase points out, your code is missing group by for all columns. When your SQL works, i asume, that you work in MariaDB, which takes in this case first found element (equivalent of ORALCE's TOP())

In your scenario, you should also have some kind of integrity rule, which will prevent from having multiple users (user_id) with same race number (race_number). If you had thouse records in your database, the rows would start duplicating.

Also be aware, that some databases (like MariaDB) can have problems with table called user because table named like that is used for authentication into database (in information_scheme)