i have problem with my SQL query.
I have job table and job_feature. Now each Job object has many feature
Example: Job 1 has feature 1, 2, 9.
So now i need that query:
Select job has feature (1 or 2) and 9.
Select job has feature (1 or 2 or 3) and (9 or 10)
FROM `m_job` as job
JOIN `d_job_feature` as jf ON job.id= jf.job_id
WHERE (jf.feature_id = 1 OR jf.feature_id = 2)
AND jf.feature_id = 9;
The problem with your query is the
feature_id for a given row cannot equal 1 or 2 and also 9. Instead you need to group your features with
group by. Then you can use
condition aggregation to see if both conditions can exist for a given feature.
Here's one option using
select * from m_job as m where exists ( select 1 from d_job_feature as d where m.id=d.job_id group by d.job_id having max(case when d.feature_id in (1,2,3) then 1 else 0 end) = 1 and max(case when d.feature_id in (9,10) then 1 else 0 end) = 1 )