sql select, get cars which are due a job

My database consists of 2 tables. Im using MySQL.

Table 1

cars = id(int,PRIMARYKEY), reg(int), type(int)

Table 2

jobs = ref(int,PRIMARYKEY), id (int), date(date)

I need a SELECT statement which would get me all the cars which have not had a job in th开发者_如何转开发e past 3 weeks.

(i.e: If a car has not had a job in the past 3 weeks, then that car(id) would be returned.)

I know there would be several ways to do this however I am not up to speed in sql yet :<

This is a good use case for NOT EXISTS:

select c.id,c.reg,c.type
from cars c
where not exists 
(
  select NULL
  from jobs j
  where j.id = c.id
  and j.date >= CURRENT_DATE - INTERVAL 3 WEEK
);

I don't know what flavour of Oracle you are using but this would work on Oracle. In other flavours you'd have to work out your date in a different way (and maybe the join syntax is a little different).

select *
from cars c
 left join jobs j 
  on j.id = c.id
where j.date < sysdate - 21

If the link between both tables is the jobs.ref, then the sql could be like this:

   select id from cars where id in 
    (select ref from jobs where date < DATE_SUB(NOW(),INTERVAl 21 DAY)