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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of mySQL query that changes team id to team name and makes not null blank without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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
We are here to answer your question about mySQL query that changes team id to team name and makes not null blank - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji