agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables
Date: Thu, 12 Jun 2014 22:07:46 +0200 (CEST)
Message-ID: <VisenaEmail.20f.ec5587c93533f1c7.14691ab8660@tc7-on> (raw)
In-Reply-To: <CAKFQuwatcS14kwuqTL=TtCwUQiVdSpzP4pLW0tP+L2yFSUtQMg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På torsdag 12. juni 2014 kl. 21:52:22, skrev David G Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>: On Thu, Jun
12, 2014 at 3:49 PM, David Johnston<[hidden email] <>> wrote: 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. Though I guess you could also simply chain together the
FULL OUTER join: FROM ( ( (apple OUTER bananas) AS ab OUTER pears ) AS abp
OUTER pineapples ) AS abpp David J. By OUTER, do you mean FULL OUTER JOIN
here? I'm unsure how to write the correct ON-clause of my FULL OUTER JOINs.
Do you know how? There will be more derived tables to FULL OUTER JOIN with so
I need something robust, just don't know how to do it. Thanks. -- Andreas
Jospeh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
view thread (10+ messages) latest in thread
Message-ID: <VisenaEmail.20f.ec5587c93533f1c7.14691ab8660@tc7-on>
Permalink: ../VisenaEmail.20f.ec5587c93533f1c7.14691ab8660@tc7-on/
Also on: postgresql.org/message-id/VisenaEmail.20f.ec5587c93533f1c7.14691ab8660@tc7-on
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables
In-Reply-To: <VisenaEmail.20f.ec5587c93533f1c7.14691ab8660@tc7-on>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox