MySQL Having clause ignore id

I am trying to insert a non duplicate job_id into review_job but having clause and joins are not working properly.

Please consider this query

IN开发者_如何学GoSERT INTO review_jobs (job_id) 
SELECT B.JOB_ID FROM (
    SELECT A.job_id AS JOB_ID FROM jobs A 
    LEFT JOIN review_jobs RJ ON A.job_id=RJ.job_id 
    WHERE RJ.job_id IS NULL 
    HAVING A.post_date=MIN(A.post_date) 
    LIMIT 1
) B;

The above query bring the same job_id which is already present in review_jobs if I remove having clause otherwise it does not insert. My goal is to put oldest job in review_jobs table.

Do you want this?

Oldest job_id from jobs that isn't already in review_jobs

If so, try this

INSERT INTO (job_id) 
SELECT A.job_id
FROM jobs A 
WHERE NOT EXISTS (SELECT * 
         FROM review_jobs RJ
         WHERE A.job_id=RJ.job_id)
ORDER BY A.post_date
LIMIT 1;

A couple of points:

1. You do not need to wrap the query in a sub-query when using an INSERT ... SELECT
2. You can use a combination of ORDER BY and LIMIT to get the first or last n records.

So the query becomes:

INSERT INTO review_jobs (job_id) 
    SELECT A.job_id
    FROM jobs A 
        LEFT JOIN review_jobs RJ ON A.job_id = RJ.job_id 
    WHERE RJ.job_id IS NULL
    ORDER BY A.post_date
    LIMIT 1

To find the oldest job, you can use order by:

insert  review_jobs
        (job_id)
select  j.job_id
from    jobs j
left join
        review_jobs rj
on      j.job_id = rj.job_id
where   rj.job_id is null
order by
        j.post_date
limit   1

HAVING is a group selector, and you don't have a GROUP BY in your statement. Try the following instead:

...WHERE RJ.job_id IS NULL ORDER BY A.post_date ASC LIMIT 1

You could also use a subquery, e.g.

...WHERE RJ.job_id IS NULL AND NOT EXISTS (SELECT job_id FROM jobs WHERE post_date < A.post_date)