Mysql : using Join instead of subQuery

Question

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

nacho · Accepted Answer · 2018-05-11 12:09:07Z

1

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

answered May 11, 2018 at 12:09

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abdes Over a year ago

it works when i change it to : join teams t1 on t1.id=games.team_home join teams t2 on t2.id=games.team_away Thanks (Y)

Collectives™ on Stack Overflow

Mysql : using Join instead of subQuery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related