Avoiding duplicates when joining a table to itself

Question

I'm sure the answer is already out there but I'm struggling to apply similar-enough posts to my specific problem. Please bear with me.

I have a jobs table that references itself with the id_parent_job field. Child jobs have a number of openings.

id  id_parent_job  is_active  num_openings
1   1              y          NULL
2   1              n          15
3   1              y          10
4   4              y          NULL
5   4              n          13
6   6              y          NULL
7   6              y          15
8   6              n          15
9   6              y          15

Given a parent job id, I want to find 2 figures: the sum of openings and the sum of active openings. Here are the desired results. I need help with the query.

job 1: 25 openings, 10 active openings
job 4: 13 openings, 0 active openings
job 6: 45 openings, 30 active openings

Below is the query I'm working with, but it leads to duplicates. This is (maybe?) a good explanation of the problem.

SELECT jobs.id, SUM(childjobs.num_openings), SUM(activechildjobs.num_openings) FROM jobs
    LEFT JOIN jobs AS childjobs
        ON childjobs.id_parent_job = jobs.id
        AND childjobs.id != jobs.id
    LEFT JOIN jobs AS activechildjobs
        ON activechildjobs.id_parent_job = jobs.id
        AND activechildjobs.id != jobs.id
        AND activechildjobs.is_active = 'y'
    WHERE jobs.id = 1

Here are the incorrect results.

job 1: 25 openings, 20 active openings
job 4: 13 openings, 0 active openings
job 6: 90 openings, 90 active openings

Jobs with more than one child are counting children multiple times.

Nick · Accepted Answer · 2020-05-04T13:00:05.860

1

You can use conditional aggregation and group by id_parent_job to avoid the JOINs that are causing duplication of records (and hence the incorrect counts):

SELECT id_parent_job AS id, 
       SUM(num_openings) AS openings,
       SUM(CASE WHEN is_active = 'y' THEN num_openings ELSE 0 END) AS active_openings
FROM jobs
GROUP BY id_parent_job

Output:

id  openings    active_openings
1   25          10
4   13          0
6   45          30

Demo on dbfiddle

edited May 04 '20 at 13:00

answered May 04 '20 at 12:46

Nick

138,499
22
57
95

My hero! Concise. Effective. Thank you. – Taylor Vance May 04 '20 at 12:54
@TaylorVance just realised I'm an idiot. I've updated the answer with the proper query. – Nick May 04 '20 at 13:01
why the change? Was something wrong with the original? It seemed to fit my needs better because I think I need to come at it FROM the parent job starting point, rather than the child job as your edit is doing. Long story, there's lots of infrastructure I can't change in building the query. I really only have control over the SELECT and the JOIN portions of the query. The jobs.id = 123 (123 being the parent) is already baked in. – Taylor Vance May 04 '20 at 13:10
I just realised the parent table in the join wasn't adding anything. If you think the structure works better for you in its original form though I can revert the edit. – Nick May 04 '20 at 13:19
That makes sense. Yes the original fits my situation better, but this way might be better generally. Maybe add both? Up to you. – Taylor Vance May 04 '20 at 13:50

Avoiding duplicates when joining a table to itself

1 Answers1