dungphanxuan dungphanxuan -4 years ago 88
SQL Question

MYSQL Query for more condition

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)
if using this query

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;

=> return empty data

So now, how can i using SQL for query this data.

Answer Source

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 exists:

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 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download