CODEWITHSUNDEEP
mysqlsqlone-to-many
Devin
Devin

Reputation: 2133

Filter on the Many side of a One-To-Many Relationship

I've got two tables: jobs and job_industries (joined on jobs.id=job_industries.job_id). I'd like to find every job with jobs.title like '%Finance%' and without job_industries.industry=1.

However, when running the query below, I'm getting a list of every job that matches the title criteria and has at least one industry option that isn't 1.

select jobs.id, title, industry
from `job_industries`
left join jobs on jobs.id=`job_industries`.job_id
where is_live=1 and jobs.`is_closed`=0 and 1 in (select industry from job_industries where job_id in )

e.g. if a job has two job_industry relations (industry=1 and industry=2), then this job will match the query. I'd prefer to find jobs that don't.

Upvotes: 0

Views: 596

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

SELECT j.id, j.title, ji.industry
    FROM jobs j
        INNER JOIN job_industries ji
            ON j.id = ji.job_id
                AND ji.industry <> 1
    WHERE j.title LIKE '%Finance%'
        AND j.is_live = 1
        AND j.is_closed = 0
        AND NOT EXISTS (SELECT 1
                            FROM job_industries ji2
                            WHERE ji2.job_id = j.id
                                AND ji2.industry = 1);

Alternatively, you could do this instead:

SELECT j.id, j.title, ji.industry
    FROM jobs j
        INNER JOIN job_industries ji
            ON j.id = ji.job_id
                AND ji.industry <> 1
        LEFT JOIN job_industries ji2
            ON j.id = ji2.job_id
                AND ji2.industry = 1
    WHERE j.title LIKE '%Finance%'
        AND j.is_live = 1
        AND j.is_closed = 0
        AND ji2.job_id IS NULL;

Upvotes: 4

Eugen Rieck
Eugen Rieck

Reputation: 65264

select jobs.id, title, industry
from `job_industries`
left join jobs on jobs.id=`job_industries`.job_id AND job_industries.industry=1
where is_live=1 and jobs.`is_closed`=0 and job_industries.industry IS NULL

Upvotes: 0

Related Questions