Unknown column in 'on clause'

[edm73]

Hi, complete newbie and novice here so bear with me.

 

I am getting the error < Unknown column 'S.SeasonID' in 'on clause' > here:

http://www.fleethistory.co.uk/stats/player.php?id=127&oppid=

 

When it should look like this:

http://mercury.nethostcomputer.com/~fleet/stats/player.php?id=127&oppid=

 

From searching these forums, I've worked out it's because the offending site is using MySQL5 and also something to do with commas and LEFT JOINs (as I said, I'm a novice!).

 

And I'm guessing this is where I need to change it:

 

{
    $get_seasons = mysql_query("SELECT SE.SeasonName AS name, S.SeasonID AS id, COUNT( A.AppearancePlayerID ) AS apps
    FROM tplss_seasons S, tplss_seasonnames SE
    LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
    LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = '$id' AND A.AppearanceSeasonID = S.SeasonID AND A.AppearanceMatchID = M.MatchID
    WHERE SE.SeasonID = S.SeasonID AND S.SeasonPlayerID = '$id'
    GROUP BY S.SeasonID
    ORDER BY name",$connection)
    or die(mysql_error());

 

Is anyone able to tell me exactly what I need to replace to get this working?

[fenway]

Change

 

FROM tplss_seasons S, tplss_seasonnames SE

 

to

 

FROM (tplss_seasons S, tplss_seasonnames SE)

[eran]

This is why you shouldn't mix join syntax. Try fenway's suggestion, or change all the joins to explicit joins -

 

Change:

FROM tplss_seasons S, tplss_seasonnames SE

 

To:

FROM tplss_seasons AS S

INNER JOIN tplss_seasonnames AS SE ON SE.SeasonID = S.SeasonID

 

And make sure to remove that condition from the WHERE clause

[edm73]

Thanks both - I tried the first one but no luck.

 

This is why you shouldn't mix join syntax. Try fenway's suggestion, or change all the joins to explicit joins

 

Could you tell me how to do that? I am using third-party software of which I usually just use the admin back-end. The software developer no longer offers any support and I've never had to look into the nuts and bolts of the PHP pages until this problem occurred.

[eran]

I showed you exactly how in my previous response. You join the first two tables using the implicit syntax (separating them with a comma) and the rest using the explicit (specifying the join type and on clause). I suggested you change the implicit syntax to the explicit syntax and wrote the exact statements to use