MySQL加入查询不起作用
问题描述：
我正在使用CodeIgniter和MySQL。我已经job_post表，看起来象下面这样：MySQL加入查询不起作用
在这里，我有一个像这样写查询：
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
当我有关城ID为1的jp.city in (1)比它给人完美的结果，但是当我在jp.city in (2)通过城市ID为2。
那么我的查询应该改变什么？
注意：当我通过城市id 2比它显示第一个和最后一个记录。但现在它不起作用。
答
使用c.city_id而不是在where子句中使用jp.city。
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
答
更新where子句
where jp.city like '%2%' and jp.is_delete = 1
，或者如果它包含1,2和11,12下方使用等
where jp.city like '%1%2%' and jp.city not like '%11%12%' and jp.is_delete = 1
但使用它不是最优的，所以请保持的ID为一对多relationshiop
可能是ID不一致PLZ检查你的IDS – Karthi
使用jp.city LIKE“％2％”作为不会给正确的结果 –
节省逗号分隔不好用一对多的关系 –