Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: 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 23:28:33 +0200 (CEST)
Message-ID: <VisenaEmail.224.f0e0ebce2fcc534b.14691fa99d2@tc7-on> (raw)
In-Reply-To: <CAKFQuwYbHmp5sr-oTn0SEaTq-AcuA3H3gA1AOSDSviGXrRR_qA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

På torsdag 12. juni 2014 kl. 22:36:23, skrev David G Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>: [snip]   WITH 
a_src (companyid, projectid, a_count) AS ( SELECT companyid, 
COALESCE(projectid,'N/A'), a_count FROM company_a) , b_src (companyid, 
projectid, b_count) AS ( SELECT companyid, COALESCE(projectid,'N/A'), b_count 
FROM company_b) , left_master AS ( SELECT DISTINCT companyid, projectid FROM 
a_src UNION DISTINCT SELECT DISTINCT companyid, projectid FROM b_src) SELECT 
companyid, projectid, a_count, b_count FROM left_master LEFT JOIN a_src USING 
(companyid, projectid) LEFT JOIN b_src USING (companyid, projectid) ;   If it 
is too slow to derive left_master you can consider adding triggers to the 
company_product tables to maintain a separate table of known combinations.   
The chaining version:   WITH  a_src (companyid, projectid, a_count) AS ( 
SELECT companyid, COALESCE(projectid,'N/A'), a_count FROM company_a) , b_src 
(companyid, projectid, b_count) AS ( SELECT companyid, 
COALESCE(projectid,'N/A'), b_count FROM company_b) , c_src (companyid, 
projectid, c_count) AS ( SELECT companyid, COALESCE(projectid,'N/A'), c_count 
FROM company_c) SELECT companyid, projectid, a_count, b_count, c_count FROM 
(a_src FULL JOIN b_src USING (companyid, projectid)) ab_src FULL JOIN c_src 
USING (companyid, projectid)) abc_src ;   Though you could also test whether 
the following is faster:   a_raw FULL JOIN b_raw ON ((a_raw.companyid, 
COALESCE(a_raw.projectid, 'N/A')) = (b_raw.companyid, COALESCE(b_raw.projectid, 
'N/A')))       Alternatively...go vertical:   WITH a_src (companyid, projectid, 
item_count, item_type) AS ( SELECT companyid, COALESCE(projectid,'N/A'), 
a_count, 'A' FROM company_a) , b_src (companyid, projectid, item_count, 
item_type) AS ( SELECT companyid, COALESCE(projectid,'N/A'), b_count, 'B' FROM 
company_b)   SELECT *  FROM a_src   UNION ALL   SELECT * FROM b_src ;   David J.
    Your chaining version with WITH was the only one I could get to work. I 
think that is the cleanest version as it wraps the rather large query behind 
the derived tables in a readable fashion.   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)

Message-ID: <VisenaEmail.224.f0e0ebce2fcc534b.14691fa99d2@tc7-on>
Permalink:  ../VisenaEmail.224.f0e0ebce2fcc534b.14691fa99d2@tc7-on/
Also on:    postgresql.org/message-id/VisenaEmail.224.f0e0ebce2fcc534b.14691fa99d2@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.224.f0e0ebce2fcc534b.14691fa99d2@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