mysql select query issue

We have a DB like this:

CREATE TABLE `jobs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `job` varchar(255),
  PRIMARY KEY (`id`)
);

INSERT INTO `jobs` VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

CREATE TABLE `payments` (
  `job_id` int,
  `amount` int 
);

INSERT INTO `payments` VALUES
(1,100),
(1,100),
(2,600),
(2,600);

Our task is:开发者_如何学编程

Get all jobs, where sum of payments is smaller than 1000.

As a result we should jobs 'a','c' and 'd'. But our query:

SELECT job 
FROM jobs j
JOIN payments p ON j.id=p.job_id
GROUP BY job_id
HAVING sum(amount) < 1000;

excludes jobs without any payments. So a result we get only 'a'.

How should we construct the query to get all jobs where sum of payments is smaller than 1000?

---

I think you might need a LEFT JOIN

SELECT job 
FROM jobs j LEFT JOIN payments p ON j.id=p.job_id
GROUP BY job_id 
HAVING sum(amount) < 1000;

---

left join will work, as long as you use a case statement to ensure that the amount is calculated as zero for jobs with no payments. Otherwise, amount will be null, and therefore cannot be compared to 1000 in the HAVING clause.

SELECT j.id job_id ,sum(case when amount is null then 0 else amount end)
FROM jobs j LEFT JOIN payments p ON j.id=p.job_id
GROUP BY j.id
HAVING sum(case when amount is null then 0 else amount end) < 1000;

N.B. this works on oracle, not sure about exact syntax on mysql.

---

You need to join job to the result of the sum query:

SELECT * 
from job j
join (SELECT job_id, sum(amount) 
    FROM jobs j
    JOIN payments p ON j.id=p.job_id
    GROUP BY job_id
    HAVING sum(amount) < 1000) x on x.job_id = j.job_id
union
SELECT * from job where job_id not in (select job_id from payments);

The union also finds jobs that have NO payments