In select query include placeholder results where no direct result was found

41
February 12, 2019, at 11:30 AM

I have a table of race results. Not all competitors enter every single race, but I would like to include a table which shows those races they didn't enter with blanks and a hyphen "-" value in the result column.

So, for example, the competitor "John Smith" did not enter a race in either 2004 or 2005, but I want these events to be printed, even though his name does not appear against the records for those races in the database.

I only want this to happen for Race Series ("srs) and years where he did at least enter one race in that year's series of races. So, he didn't do any races in 2003 so not even a hyphen result is shown.

I have produced this code thus far, but it's not working as intended - it is including both the competitors result AND a hyphen for the events he did compete in.

The below should help explain the type of result structure I seek to achieve:

I have tried the query shown using a UNION. I think I am (hopefully) missing a small trick.

select series as srs, 
year as yr, 
concat(year,series) as yrsrs, 
concat(year,series,round) as yrsrsrd, 
round, 
venue,
result
from races
where competitor = "John Smith"
group by yrsrsrd limit 200
UNION
select series as srs, 
year as yr, 
concat(year,series) as yrsrs, 
concat(year,series,round) as yrsrsrd, 
round, 
venue,
case when competitor <> "John Smith" then "-" else result end
from races
group by yrsrsrd order by 2, 1, 5 limit 200
Answer 1

You only need the second part of your query, but need to change the calculation of the result.

select series as srs, 
year as yr, 
concat(year,series) as yrsrs, 
concat(year,series,round) as yrsrsrd, 
round, 
venue,
coalesce(sum(case when competitor = "John Smith" then result end), "-") as result
from races
group by yrsrsrd 
order by 2, 1, 5
limit 200

Now look at this line from inside to outside:

coalesce(sum(case when competitor = "John Smith" then result end), "-") as result

The CASE expression will return the result only for John Smith. For all other rows in the same group (race) it will be NULL. The SUM function will ignore all NULLs and only keep the result of J.S. If J.S. has not participated in that race, all elements of SUM will be NULL, and so SUM will return NULL. At the end COALESCE will convert NULL to "-".

Answer 2

You can use the mysql COALESCE function: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce

This function will help you set a placeholder value. (e.g.: COALESCE(result,'-')

Using a join statement, you could do somthing like that:

SELECT 
year,
yrsrs,
yrsrsrd,
round, 
venue,
COALESCE(result,'-') as result
FROM (
    SELECT year as yr, concat(year,series) as yrsrs, concat(year,series,round) as yrsrsrd, round, venue 
    FROM races
    GROUP BY yrsrsrd order by 2, 1, 5 limit 200
) a 
LEFT JOIN (
    SELECT result, concat(year,series,round) as yrsrsrd 
    FROM races 
    WHERE competitor = "John Smith"
) b 
ON a.yrsrsrd = b.yrsrsrd

Disclaimer: This SQL statement is untested and might have some issues.

READ ALSO
mysql - current_timestamp and null

mysql - current_timestamp and null

I have question about mysql configurationI have the same database on AWS and local apache server on ubuntu

29
How to export a database (.sql) file to remote database through wifi? [on hold]

How to export a database (.sql) file to remote database through wifi? [on hold]

I have external database where I have export thesql file to my local now I want to import the same database through terminal by using wifi to another

22
how can i structure posts, shared posts and pages table in mysql database?

how can i structure posts, shared posts and pages table in mysql database?

I am working on a social networking site and i need to structure my POSTS table, SHARED_POSTS table and PAGES table and i don't know the best way to go about thisi have my USERS table and each post being made by a user is stored in the POSTS table with the current...

45