Why have I had to change this query after upgrading to MySQL5?

[MargateSteve]

I have just upgraded from MySQL 4 to 5 and everything seems to have been ok except for one query.

 

This used to work fine

SELECT *,
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(goals.goal_id) AS tgls
FROM goals, games 
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC LIMIT ".$limit."

 

but that now causes the query to show the fields to fail. I have taken out the join and this following query works

SELECT *,
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(goals.goal_id) AS tgls
FROM goals, games, players 
WHERE goals.scorer = players.player_id
AND goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC LIMIT ".$limit."

but just wondered what I need to do differently in future.

 

Steve

 

EDIT**

I actually seem to have queries failling everywhere. It seems to be anywhere that there is a join

SELECT * FROM (SELECT DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate, 
HT.team_name as HomeTeam, 
VT.team_name as AwayTeam, 
COMP.comp_short_name as CP
FROM all_games
JOIN teams as HT ON all_games.home_team = HT.team_id
JOIN teams as VT ON all_games.away_team = VT.team_id 
JOIN competitions as COMP ON all_games.comp = COMP.comp_id 
WHERE 
$id IN (all_games.home_team, all_games.away_team) 
AND all_games.home_goals IS NOT NULL 
ORDER BY `date` DESC 
LIMIT 5) as tbl ORDER BY `date` asc

[MargateSteve]

I think that I have worked it out but please correct me if I am wrong.

 

In MySQL 4 you could place the joins after all the FROM tables were selected

FROM goals, games 
INNER JOIN players ON goals.scorer = players.player_id

In MySQL 5 you have to place the join directly after the FROM table

FROM goals INNER JOIN players ON goals.scorer = players.player_id, games 

 

Steve

[fenway]

If you change

 

FROM goals, games

 

to

 

FROM (goals,games)

 

you'll be ok.

 

In general, don't mix comma and JOIN syntax.