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

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_name	project_name	num_apples	num_bananas	num_pineapples	num_pears
1	C1	NULL	2	2	10	NULL
2	C1	P1	5	12	NULL	NULL
3	C1	P2	2	NULL	NULL	NULL
4	C2	NULL	3	NULL	10	NULL
5	C2	P1	3	NULL	NULL	NULL
6	C2	P2	3	NULL	NULL	NULL
7	C3	NULL	NULL	8	NULL	NULL
8	C3	NULL	NULL	NULL	NULL	7

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]

David J.