Getting the syntax right for MySQL select with inner join Using MySQL version 5.1.73 and phpmyadmin 4.0.10.16.

I am unable to get this past the syntax checker:

select * from courses as co , course_type as ct 
left join course_leaders as cl on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id 
where co.course_type_id = ct.course_type_id
order by short,start_date ASC


It insists that there is no column "co.course_leaders_id1" which there is indeed.

Any ideas anyone?

ok, this is the result I came up (with the help from below - thanks)

select * from courses as co
left join course_leaders as cl on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id 
inner join course_type as ct on co.course_type_id = ct.course_type_id
order by short,start_date ASC
Yes, problem one is that you are mixing explicit (SQL-92) JOIN syntax with implicit join, , (SQL-89) comma syntax. While this is allowed and with some care it may even work correctly, it's very easy to get this type of errors.

The FROM part of your query:

from courses as co , course_type as ct 
left join course_leaders as cl 
    on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli 
    on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id 


is parsed as:

from 
    courses as co 
  ,                        -- &lt;-- that acts as a CROSS JOIN
    (
        (
            course_type as ct 
        left join 
            course_leaders as cl 
        on 
            cl.course_leaders_id = co.course_leaders_id1
        )
    left join 
        course_links_to_innstyle as cli 
    on 
        cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id 
    )


Now it's clear that inside the parentheses, co is not defined.



Problem two is that there is no joining condition for ct. You have 4 tables but only 2 (and not 3) joining conditions. This is either something you missed or the intention of the query is to do a CROSS JOIN.



Solution:


It's better to use explicit JOIN syntax everywhere and not mix the two (syntaxes).
Fix the code by either adding the missing condition or use CROSS JOIN if that is the intention of the query.