CODEWITHSUNDEEP
mysqljoinsubquery
Abdes
Abdes

Reputation: 986

Mysql : using Join instead of subQuery

This query work fine but how i can use join instead of subquery ?

SELECT  
    games.id, 
    games.team_home,
    games.score_home,
    games.team_away,
    games.score_away,
    (SELECT teams.name FROM teams WHERE teams.id=games.team_home ) as t1,
    (SELECT teams.name FROM teams WHERE teams.id=games.team_away ) as t2
FROM 
    games, teams 
WHERE  
    games.date_game = '2018-06-15'  
GROUP BY
    games.id

Upvotes: 0

Views: 31

Answers (1)

nacho
nacho

Reputation: 5397

You can do it this way, with two JOINS on the id:

SELECT games.id,
       games.team_home,
       games.score_home,
       games.team_away,
       games.score_away,
       t1.name,
       t2.name
FROM games join teams t1 on teams.id=games.team_home 
           join teams t2 on teams.id=games.team_away 
WHERE games.date_game='2018-06-15'  
group by games.id

Upvotes: 1

Related Questions