phpmysqlcodeigniterjoinfind-in-set

CodeIgniter SELECT query with JOIN conditions using FIND_IN_SET() is not returning the expected results

I am working with CodeIgniter and MySQL. I have a job_post table that looks like below:

company_email skill city role
nilesh@webbrainstechnologies.com 2,3 1,2 5
2,3,12,13 1 6
4,5 1,4 6
parthweb@mail.com 1 1 4
parthweb@mail.com 1 1 4
1 1 4
parthweb@mail.com 1 1,2,4 4

Here I have write query like this :

select jp.*,u.first_name,u.last_name,group_concat(DISTINCT s.skill) as sk,group_concat(DISTINCT c.name) as ct,ufj.fav_id,ufj.is_favourite 
from job_post as jp 
left join industry as ind on ind.ind_id = jp.industry_id 
left join city c ON(FIND_IN_SET(c.city_id, jp.city) > 0) 
left join skill s ON(FIND_IN_SET(s.skill_id, jp.skill) > 0) 
join users as u on u.user_id = jp.emp_id 
left join user_favourite_job as ufj on ufj.job_id = jp.job_id and ufj.user_id = 8 
where jp.city in (2) and jp.is_delete = 1 group by job_id

When I have pass city id as 1 as jp.city in (1) then it gives a perfect result, but not when I pass city id as 2 as jp.city in (2).

What should I change in my query?

When I passed city id 2 it should display the first and last record from my sample set.

Solution

  • USe c.city_id instead of using jp.city in where Clause.

    select jp.*,u.first_name,u.last_name,group_concat(DISTINCT s.skill) as sk,group_concat(DISTINCT c.name) as ct,ufj.fav_id,ufj.is_favourite 
from job_post as jp 
left join industry as ind on ind.ind_id = jp.industry_id 
left join city c ON(FIND_IN_SET(c.city_id, jp.city) > 0) 
left join skill s ON(FIND_IN_SET(s.skill_id, jp.skill) > 0) 
join users as u on u.user_id = jp.emp_id 
left join user_favourite_job as ufj on ufj.job_id = jp.job_id and ufj.user_id = 8 
where c.city_id in (2) and jp.is_delete = 1 group by job_id