MySQL query not works in a different version

Question

Below query works in MySQL(Ver.4.1.22) but the same query gives error in newer version(5.5.56-MariaDB). Can anyone please help me on this.

SELECT 
                    c3.label_id
                FROM 
                    label_based_costs c3, label_based_text s3, platform o3, basic b3, 
                    series r3
                    LEFT JOIN platform o2 ON o3.content_code = o2.content_code AND o2.year = '2018'
                    LEFT JOIN basic b2 ON b3.bs_code = b2.bs_code AND b2.year = '2018'

Error message:

Error in query (1054): Unknown column 'o3.content_code ' in 'on clause'

What does your table schema look like? – WillardSolutions May 04 '18 at 13:32 — WillardSolutions, May 04 '18 at 13:32

Thomas G · Accepted Answer · 2018-05-04T13:45:02.680

This is because you are mixing EXPLICIT and IMPLICIT join Syntax.

Implicit join syntax is deprecated and you should stop using it.

Mixing both syntax in the same query is even worst and cause errors like this in recent versions MySQL (Version 4 is very old nowadays...).

Your query should be

SELECT 
    c3.label_id
FROM 
    label_based_costs c3
    CROSS JOIN label_based_text s3
    CROSS JOIN platform o3
    CROSS JOIN basic b3
    CROSS JOIN series r3
    LEFT JOIN platform o2 ON o3.content_code = o2.content_code AND o2.year = '2018'
    LEFT JOIN basic b2 ON b3.bs_code = b2.bs_code AND b2.year = '2018'

On a side note, all those CROSS JOINs are suspect because you are creating a huge cartesian product between 5 tables, and it probably needs to be reviewed

It works! hope this is will provide the same exact result in all cases! — iqbalmp, May 04 '18 at 13:44

MySQL query not works in a different version

1 Answers1