MySQL Query to Retrieve Team Schedule

[RyanMinor]

I am trying to retrieve schedules for each team based on my table below, but I am having some trouble getting the team names to display. Here are my tables my table:

 

TEAM

team_id

team_name

team_mascot

etc.

 

GAME

game_id

game_date

game_home_team

game_visitor_team

game_home_score

game_visitor_score

game_complete

 

Here is my current query. I am able to get the team's id values with this but I need the team names as id values won't do me much good.

 

SELECT game_home_team, game_visitor_team, game_date

FROM game

INNER JOIN team home ON home.team_id = game_home_team

INNER JOIN team visitor ON visitor.team_id = game_visitor_team

WHERE game_home_team = ? OR game_visitor_team = 6

ORDER BY game_date ASC;

 

How can I retrieve the team names with this query and also display a result such as W or L depending on the score for the game? Thanks so much in advance.

[Barand]

Something like this

 

SELECT home.team_name, 
CASE WHEN game_home_score > game_visitor_score THEN 'W' WHEN game_home_score < game_visitor_score THEN 'L' ELSE 'D' as home_result,
visitor.team_name, 
CASE WHEN game_home_score > game_visitor_score THEN 'L' WHEN game_home_score < game_visitor_score THEN 'W' ELSE 'D' as visitor_result,
game_date,
FROM game
INNER JOIN team home ON home.team_id = game_home_team
INNER JOIN team visitor ON visitor.team_id = game_visitor_team
WHERE game_home_team = 6 OR game_visitor_team = 6
ORDER BY game_date ASC;

[ignace]

SELECT home.team_name, visitor.team_name, game_date, IF(game_home_score > game_visitor_score, 'W', 'L') AS WinLose
FROM game
INNER JOIN team home ON home.team_id = game_home_team 
INNER JOIN team visitor ON visitor.team_id = game_visitor_team
WHERE game_home_team = ? OR game_visitor_team = 6 
ORDER BY game_date ASC;

 

EDIT: use Barand's solution, it's more extensive.