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;
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.
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