mysql select query issue

Question

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?

Dirk · Accepted Answer · 2011-08-07 00:28:05Z

2

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;

answered Aug 7, 2011 at 0:28

Dirk

3,1032 gold badges37 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomasz Jureczko Over a year ago

Left join doesn't help in this case.

steve godfrey · Accepted Answer · 2011-08-07 15:13:34Z

0

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.

edited Aug 7, 2011 at 15:13

answered Aug 7, 2011 at 15:07

steve godfrey

1,2447 silver badges15 bronze badges

Comments

Bohemian · Accepted Answer · 2011-08-07 00:56:25Z

0

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

answered Aug 7, 2011 at 0:56

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

mysql select query issue

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related