unknown column in mysql join query

Question

select concat(A.id,B.id) as id,
    B.distance_from,
    A.id as user_id,
    B.id as jobapplication_id,
    concat(A.firstname,' ',A.surname) as name,
    GROUP_CONCAT(E.category separator ',') as jobs, 
    cast(B.introduction as char(10000) character set utf8) as introduction,
    concat(F.citytown,'~',F.latitude,' ',F.longitude) as locationFacet,
    F.citytown, 
    REPLACE(F.citytown,' ','') as citytownnospaces, 
    concat(F.latitude, ',',F.longitude) as location,
    concat('CIRCLE(',F.latitude, ',',F.longitude,', d=',B.distance_from,')') as location_shape,G.province,
    B.promoted,
    B.promotion_expires,
    sum(H.rating) 
from user A, 
     job_application B,
     job_application_job_category D, 
     job_category E,
     location F, 
     province G 
right join review H 
on H.employee_id =A.id 
where A.msisdn_verified=1 
      and B.location_id = F.id 
      and F.province_id_id=G.id 
      and B.applicant_id=A.id 
      and B.id=D.job_application_id 
      and E.id=D.job_category_id 
      and B.promoted = 1 
      and B.promotion_expires > now() 
group by B.id

I am not sure why I get

ERROR 1054 (42S22): Unknown column 'A.id' in 'on clause'

Please help fix this query

Answer 1

You should not mix implicit and explicit join. You should use explicit join syntax.

Assuming you have an ID column in table A (user):

    select concat(A.id,B.id) as id
        ,B.distance_from
        ,A.id as user_id
        , B.id as jobapplication_id
        , concat(A.firstname,' ',A.surname) as name
        ,GROUP_CONCAT(E.category separator ',') as jobs
        , cast(B.introduction as char(10000) character set utf8) as introduction
        , concat(F.citytown,'~',F.latitude,' ',F.longitude) as locationFacet
        , F.citytown, REPLACE(F.citytown,' ','') as citytownnospaces
        , concat(F.latitude, ',',F.longitude) as location
        , concat('CIRCLE(',F.latitude, ',',F.longitude,', d=',B.distance_from,')') as location_shape
        ,G.province
        , B.promoted
        ,B.promotion_expires
        ,sum(H.rating) 
    from user A
    INNER JOIN  job_application B ON  B.applicant_id=A.id 
    INNER JOIN  job_application_job_category D ON B.id=D.job_application_id
    INNER JOIN  job_category E ON  E.id=D.job_category_id 
    INNER JOIN  location F ON  B.location_id = F.id 
    INNER JOIN  province G  ON  F.province_id_id=G.id 
    right join review H on H.employee_id =A.id 
    where A.msisdn_verified=1     and B.promoted = 1 and B.promotion_expires > now() 
    group by B.id