mySQL query that changes team id to team name and makes not null blank

I have made a results table for a football league called ‘games’. This results table shows the time and date, the home/away team ids and the home/away scores.

I am trying to create a query from this that displays all the information from the ‘games’ table but shows any NULL scores as blank rather than NULL. I am also trying to replace the team ids with the actual corresponding team names. The team names are stored in a table called ‘teams’ under a column called ‘tname’.

Heres what i have setup;

Creation of both teams and games tables

CREATE TABLE teams(id int primary key auto_increment,tname char(32));
CREATE TABLE games(id int primary key auto_increment, date datetime,
               hteam int, ateam int, hscore tinyint,ascore tinyint);

Adding values into both tables

INSERT INTO teams VALUES
(1,'Team1'),(2,'Team2'),(3,'Team3'),(4,'Team4');

INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),(4,'2008-1-8 20:00:00',2,4,2,1);

This query shows the structure of how i want the results table to look, however i want to change NULL to blank and team ids, e.g. Team1 = 1 to the actual team names

SELECT * FROM games;

Answer

To change NULL to empty string, you can use the COALESCE() function, which returns the first non-null argument. Alternatively, you can use the IFNULL() function to do the same job.

To return the team’s name, join the two tables based on the related ids. You will need to join the teams table twice, one for the home and another for the away.

Try this:

SELECT id, `date`, ht.tname, at.tname, COALESCE(hscore, ''), COALESCE(ascore, '')
FROM games g
LEFT JOIN teams ht ON ht.id = g.hteam
LEFT JOIN teams at ON at.id = g.ateam