Balvinder Rayat Balvinder Rayat - 1 year ago 114
SQL Question

unknown column in IN/ALL/ANY subquery where clause

I know it's documented in MYSQL DOCUMENTS That in where clause "alias" cannot be used since where clause is not populated yet. But i have to get the data matched from other table values in where clause (IN condition).

I have also read some similar kind of post but this one is quite different & big complex one. I couldn't make it working with my efforts of last 3 days.
It's showing error (excepted as per documentation)


I have to match similarly for FA_ID & PREFERRED_LOCATION_ID field

Select a.job_id, a.Employer_ID, a.Sub_user_id,
Date_format(a.creation_on,'%d-%m-%Y') as Created_date, a.Job_type,
a.Designation, a.Open_Positions, a.Job_Description, a.Min_age,
a.Max_age, a.min_exp, a.max_exp, a.Hide_Salary, a.company_name,
a.About_Company, a.Contact_person_name, a.Contact_No, a.Refresh_type,
(Select GROUP_CONCAT(DISTINCT g.Education ORDER BY pjedu.Education_ID
SEPARATOR ', ') user_education
from e_pj_edu pjedu
INNER JOIN education g ON FIND_IN_SET(g.Edu_ID, pjedu.Education_ID)
where a.job_id = pjedu.Job_ID
) as Education_ID,

from e_pj_fa uf
INNER JOIN functional_area h ON FIND_IN_SET(h.FA_ID, uf.FA_ID)
where a.Job_ID = uf.Job_ID
) as FA_ID,

(Select GROUP_CONCAT(DISTINCT i.Industry_description ORDER BY
ui.Industry_ID SEPARATOR ', ') Industry_ID
from e_pj_industry ui
INNER JOIN industry i ON FIND_IN_SET(i.Industry_ID, ui.Industry_ID)
where a.Job_ID = ui.Job_ID
) as Ind_ID,

(Select GROUP_CONCAT(DISTINCT j.location_name ORDER BY
upl.Location_ID SEPARATOR ', ') Location_ID
from e_pj_locations upl
INNER JOIN locations j ON FIND_IN_SET(j.location_id, upl.Location_ID)
where a.Job_ID = upl.Job_ID
) as Preferred_Location_ID,

(Select GROUP_CONCAT(DISTINCT uk.Keyword_Name ORDER BY uk.Keyword_ID
SEPARATOR ', ') keyskills
from e_pj_keywords uk
where a.Job_ID = uk.Job_ID
) as Keyword_Name,

GROUP_CONCAT(DISTINCT cc.salary_description ORDER BY cc.salary_ID
SEPARATOR ', ') Min_salary,

GROUP_CONCAT(DISTINCT dd.salary_description ORDER BY dd.salary_ID
SEPARATOR ', ') Max_salary

from post_jobs a
INNER JOIN user_salary cc ON FIND_IN_SET(cc.salary_ID, a.Min_salary)
INNER JOIN user_salary dd ON FIND_IN_SET(dd.salary_ID, a.Max_salary)
WHERE a.Designation LIKE '%MIS%' or a.company_name LIKE '%MIS%'
And a.max_exp <= 9
And a.Max_salary<=110
And Ind_ID IN (10001,10002,10004)
And FA_ID IN(1001)
group by a.job_id

www www
Answer Source

First thing that comes in my mind is just move your aliased where conditions to outer query, i.e.:

select * from ( 
   Select a.job_id ....
   WHERE a.Designation LIKE '%MIS%' or a.company_name LIKE '%MIS%' 
      And a.max_exp <= 9 
      And a.Max_salary<=110 
   group by a.job_id
) inner 
Ind_ID IN (10001,10002,10004)
And FA_ID IN(1001)