CODEWITHSUNDEEP
mysqljoin
Taylor Vance
Taylor Vance

Reputation: 1001

Avoiding duplicates when joining a table to itself

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.

Upvotes: 0

Views: 40

Answers (1)

Nick
Nick

Reputation: 147216

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

Upvotes: 1

Related Questions