Joining two mysql querys with mysql

Question

Query 1

$query = "SELECT  match_id, match_date, home_team, 
    home_score, away_score, away_team, rating

              FROM premier_league2 
              WHERE (home_team ='" .$home_team ."'
              OR    away_team ='" .$home_team ."')  
              AND   postponed !=1
              AND   league =1
              AND   match_date <'" . $current_date ."'
              AND   match_date >'" . $newseason ."'
              ORDER BY match_date DESC LIMIT 1";

Result

676 2013-05-19  Newcastle Utd   0   1   Arsenal -14

Query 2

SELECT home_per, draw_per, away_per FROM rating WHERE rating = -14;

Result

28.30 26.50 45.10

Now I am trying to join these query like

676 2013-05-19  Newcastle Utd   0   1   Arsenal -14  28.30 26.50 45.10

Rating (-14) is common in both tables, looking for your great help.

Please switch to [prepared statements](http://bobby-tables.com/php.html) to prevent SQL injection. — Marcel Korpel, Jun 26 '13 at 19:02

score 2 · Accepted Answer · edited May 25 '19 at 14:21

You could do it with join.

SELECT 
    a.match_id, a.match_date, a.home_team, a.home_score, a.away_score, a.away_team, a.reting, b.home_per, b.draw_per, b.away_per 
FROM
    premier_league2 a
LEFT JOIN 
    rating b ON a.rating = b.rating
WHERE 
    (home_team = %HOME_TEAM%
    OR    away_team = %HOME_TEAM%)  
    AND   postponed !=1
    AND   league =1
    AND   match_date < %SURRENT_DATE%
    AND   match_date > %NEW_SEASON%
    ORDER BY match_date DESC LIMIT 1)

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Switching string interpolation to placeholders in your answer would help by promoting good practices. This is completely devoid of SQL escaping in its current form. — tadman, Jun 26 '13 at 19:07

score 2 · Answer 2 · answered Jun 26 '13 at 19:07

SELECT
   pl.match_id, pl.match_date, pl.home_team, 
    pl.home_score, pl.away_score, pl.away_team, pl.rating,
   r.home_per, r.draw_per, r.away_per
FROM
   premier_league2 pl
 INNER JOIN rating as r ON pl.rating = r.rating
WHERE
(pl.home_team ='" .$home_team ."'
              OR    pl.away_team ='" .$home_team ."')  
              AND   pl.postponed !=1
              AND   pl.league =1
              AND   pl.match_date <'" . $current_date ."'
              AND   pl.match_date >'" . $newseason ."'
              ORDER BY pl.match_date DESC LIMIT 1";

This would be an ever better example if you changed it to use prepared statements ;-) — James Green, Jun 26 '13 at 19:09

Joining two mysql querys with mysql

2 Answers2