Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables - Mailing list pgsql-sql

From Andreas Joseph Krogh
Subject Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables
Date
Msg-id VisenaEmail.210.c1121eddda64c2ac.14691ade7d8@tc7-on
Whole thread Raw
In response to Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-sql
På torsdag 12. juni 2014 kl. 21:49:59, skrev David G Johnston <david.g.johnston@gmail.com>:
 
SELECT
    coalesce(apl.company_name, bns.company_name, pns.company_name, prs.company_name) AS company_name,
    coalesce(apl.project_name, bns.project_name, pns.project_name, prs.project_name) AS project_name,
    apl.qty                                                        AS num_apples,
    bns.qty                                                        AS num_bananas,
    pns.qty                                                        AS num_pineapples,
    prs.qty                                                        AS num_pears
FROM
 
    company_apples AS apl
 
    FULL OUTER JOIN company_bananas AS bns
        ON apl.company_id = bns.company_id
           AND (apl.project_id IS NULL AND bns.project_id IS NULL OR apl.project_id = bns.project_id)
 
    FULL OUTER JOIN company_pineapples AS pns
            ON apl.company_id = pns.company_id
               AND (apl.project_id IS NULL AND pns.project_id IS NULL OR apl.project_id = pns.project_id)
 
    FULL OUTER JOIN company_pears AS prs
            ON apl.company_id = prs.company_id
               AND (apl.project_id IS NULL AND prs.project_id IS NULL OR apl.project_id = prs.project_id)
 
ORDER BY company_name ASC, project_name ASC NULLS FIRST;
 
 
This gives the result:
 
#company_nameproject_namenum_applesnum_bananasnum_pineapplesnum_pears
1C1NULL2210NULL
2C1P1512NULLNULL
3C1P22NULLNULLNULL
4C2NULL3NULL10NULL
5C2P13NULLNULLNULL
6C2P23NULLNULLNULL
7C3NULLNULL8NULLNULL
8C3NULLNULLNULLNULL7
As you see, there are two rows for C3, which should have been 1, with num_bananas=8 and num_pears=7. There has to be something wrong with my FULL OUTER JOINs but I don't know what....
 
 
 
​Row #7 is from the right side of the outer join between apples and bananas
Row #8 is from the right side of the outer join between apples and pears
 
Since you are only using apple as the source of valid company-project pairs only those records will be "correct" per your definition.
 
Even if you do not use a CROSS JOIN between company/product you have to obtain a master list of ​valid company-project pairs from ALL of the target tables.  You can then LEFT JOIN that master against each of the target tables and be sure that you have a valid master record to attach to.
 
The direct way to do this is:
 
SELECT DISTINCT company_id, project_id FROM apples
UNION DISTINCT
SELECT DISTINCT company_id, project_id FROM pears
[and so forth]
 
The problem with retreiving a list of companies/projects is that the actual sub-queries are pretty complex and don't run instantly, so I'll end up querying lots of things twice.
 
--
Andreas Jospeh Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-sql by date:

Previous
From: David G Johnston
Date:
Subject: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables