[SOLVED] Selecting records that are not matched??

[kirk112]

Hi

 

I have to following query

 

SELECT j.job_id, jt.job_title, s.salary, lg.location_name, ls.specific_name, LEFT(jat.advert_text,1000) AS advert, DATE_FORMAT(ja.added_on, '%d-%m-%Y') AS date_added

FROM jobs AS j

LEFT JOIN job_titles AS jt ON jt.job_id = j.job_title

LEFT JOIN salary AS s ON s.salary_id = j.salary

LEFT JOIN location_general AS lg ON lg.location_id = j.location

LEFT JOIN location_specific AS ls ON ls.specific_id = j.location_specific

LEFT JOIN job_adverts AS ja ON j.job_id = ja.job_id

LEFT JOIN job_adverts_text AS jat ON ja.advert_text_id = jat.advert_text_id WHERE j.job_title IN (100,101,102,103) AND ja.advert_live = 1 ORDER BY ja.added_on

 

This selects all the jobs that have matched the query and works fine, but I also have the following table

 

jbe_jobs_sent

sent_id      (PK)

job_id        (FK) for the jobs table

jbe_id        (ID) for the jobs by email

 

What I need to do is join this table to the about query so that it only selects the rows where the jbe_jobs_sent.job_id do not match the j.job_id

 

I have tried

 

LEFT JOIN jbe_jobs_sent AS jbes ON jbes.job_id != j.job_id AND jbes.jbe_id = 3

 

but this joins against all the other records in the jbe_jobs_sent table

 

Hope this make sense

 

Thanks for your help.

 

 

 

[kirk112]

Found the answer.

 

I have to use either (simplified)

 

SELECT j.job_id

FROM jobs AS j

WHERE j.job_title IN (100,101,102,103) AND j.job_id NOT IN (SELECT job_id FROM jbe_jobs_sent WHERE jbe_id = '3') ORDER BY ja.added_on

 

or

 

SELECT j.job_id

FROM jobs AS j

LEFT JOIN jbe_jobs_sent  AS jbes ON jbes.job_id = j.job_id AND jbes.jbe_id = 3

WHERE j.job_title IN (100,101,102,103) AND jbes.job_id IS NULL

 

Is one way better that the other in terms of performance?

 

Thanks again!!

 

[fenway]

Definitely the second one -- NOT IN is terrible.

[kirk112]

Thanks Fenway