Combining 2 SQL's into one-CodePudding

$sql = "SELECT teams.id,teams.name,teams.updated
        FROM teams 
        ORDER BY teams.name ASC";
list($teams) = getrows($sql);

foreach($teams as $team)
{
    $sql = "SELECT time FROM events WHERE (home_team='".$team['id']."' OR away_team='".$team['id']."') AND (status='FT' OR status='PEN' OR status='AET') ORDER BY time DESC LIMIT 1";

So im looking to combine them in to 1 sql but im having issues trying to use the veriable from the first sql if i use LEFT JOIN (SELECT * FROM events WHERE team.id='|team from first query|') and having the orginal team id in the sub query. if that makes sence.

Thanks

CodePudding user response：

I hope I understood everything correctly

SELECT MAX(events.time) AS latest_time, teams.id, teams.name, teams.updated
FROM events 
LEFT JOIN teams ON events.home_team=teams.id OR events.away_team=teams.id
WHERE ...
ORDER BY ...
GROUP BY teams.id

group by because i think you have multiple rows per teams.id in table events. then you can extract the highest(newest) time with MAX().