help with mysql query

[jonno946]

i have 2 databases, one uses the primary key of another as the foreign key.

I want to find all of the rows in the primary table that doesn't exist in the foreign table.

e.g:

table jobs:

job_id
company_id
job_name
job_text
date
...

table companies
company_id
company_name
...

from the example above i want to find all companies that have NOT had any jobs between one date and another.

i have a query that gets all of the companies that HAVE had jobs between oine date and another but not the other way around!

thanks.

[Drakla]

SELECT company_id, company_name FROM companies c LEFT JOIN jobs j ON c.company_id = j.company_id WHERE j.company_id IS NULL AND date BETWEEN $date1 AND $date2

[kakki]

???
SELECT * FROM job_id WHERE company_id NOT IN ( SELECT company_id FROM companies )

[jonno946]

Drakla wrote:

SELECT company_id, company_name FROM companies c LEFT JOIN jobs j ON c.company_id = j.company_id WHERE j.company_id IS NULL AND date BETWEEN $date1 AND $date2

if i leaver the between clause off that works fine, otheriwse it brings up 0 results with whatever date range i select.

[Drakla]

Actually it should be this:

SELECT company_id, company_name FROM companies c LEFT JOIN jobs j ON c.company_id = j.company_id AND date BETWEEN $date1 AND $date2 WHERE j.company_id IS NULL

Can you test that with appropriate dates so you get it with and without results.

[jonno946]

excellent that works great.

i'm normally OK with SQL queries but joins confuse me lol

thanks again.