Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WvCYS-0006Is-Kx for pgsql-sql@arkaria.postgresql.org; Thu, 12 Jun 2014 21:29:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WvCYS-00083L-5G for pgsql-sql@arkaria.postgresql.org; Thu, 12 Jun 2014 21:29:04 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1WvCYQ-00083F-RP for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 21:29:03 +0000 Received: from post.officenet.no ([195.225.13.103]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1WvCYM-0008OU-Vw for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 21:29:00 +0000 Received: from [10.47.1.10] (helo=tc7-on) by post.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1WvCYH-00051B-KS for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 23:28:55 +0200 Received: from localhost ([127.0.0.1] helo=tc7-on) by tc7-on with esmtp (Exim 4.76) (envelope-from ) id 1WvCXx-0009GC-Vw for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 23:28:34 +0200 Date: Thu, 12 Jun 2014 23:28:33 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: Subject: Re: Re: Problem with duplicate rows when FULL OUTER JOIN'ing 3 derived tables MIME-Version: 1.0 X-Mailer: Visena Mail 1.9.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1, HTML_MESSAGE=0.001) X-Pg-Spam-Score: -1.9 (-) Content-Type: multipart/related; boundary="----=_Part_711_838159301.1402608513465" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_710_658337235.1402608513465 Content-Type: multipart/related; boundary="----=_Part_711_838159301.1402608513465" ------=_Part_711_838159301.1402608513465 Content-Type: multipart/alternative; boundary="----=_Part_712_950954365.1402608513493" ------=_Part_712_950954365.1402608513493 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 torsdag 12. juni 2014 kl. 22:36:23, skrev David G Johnston < david.g.johnston@gmail.com >: [snip] =C2= =A0 =E2=80=8BWITH=C2=A0 a_src (companyid, projectid, a_count) AS ( SELECT companyid,=20 COALESCE(projectid,'N/A'), a_count FROM company_a) , b_src=E2=80=8B (compan= yid,=20 projectid, b_count) AS ( SELECT companyid, COALESCE(projectid,'N/A'), b_cou= nt=20 FROM company_b) , left_master AS ( SELECT DISTINCT companyid, projectid FRO= M=20 a_src UNION DISTINCT SELECT DISTINCT companyid, projectid FROM b_src) SELEC= T=20 companyid, projectid, a_count, b_count FROM left_master LEFT JOIN a_src USI= NG=20 (companyid, projectid) LEFT JOIN b_src USING (companyid, projectid) ; =C2= =A0 If it=20 is too slow to derive left_master you can consider adding triggers to the= =20 company_product tables to maintain a separate table of known combinations. = =C2=A0=20 The chaining version: =C2=A0 =E2=80=8BWITH=C2=A0 a_src (companyid, projecti= d, a_count) AS (=20 SELECT companyid, COALESCE(projectid,'N/A'), a_count FROM company_a) , b_sr= c=E2=80=8B=20 (companyid, projectid, b_count) AS ( SELECT companyid,=20 COALESCE(projectid,'N/A'), b_count FROM company_b) , c_src=E2=80=8B (compan= yid,=20 projectid, c_count) AS ( SELECT companyid, COALESCE(projectid,'N/A'), c_cou= nt=20 FROM company_c) SELECT companyid, projectid, a_count, b_count, c_count FROM= =20 (a_src FULL JOIN b_src USING (companyid, projectid)) ab_src FULL JOIN c_src= =20 USING (companyid, projectid)) abc_src ; =C2=A0 Though you could also test w= hether=20 the following is faster: =C2=A0 a_raw FULL JOIN b_raw ON ((a_raw.companyid,= =20 COALESCE(a_raw.projectid, 'N/A')) =3D (b_raw.companyid, COALESCE(b_raw.proj= ectid,=20 'N/A'))) =C2=A0 =C2=A0 =C2=A0 Alternatively...go vertical: =C2=A0 WITH a_sr= c (companyid, projectid,=20 item_count, item_type) AS ( SELECT companyid, COALESCE(projectid,'N/A'),=20 a_count, 'A' FROM company_a) , b_src=E2=80=8B (companyid, projectid, item_c= ount,=20 item_type) AS ( SELECT companyid, COALESCE(projectid,'N/A'), b_count, 'B' F= ROM=20 company_b) =C2=A0 SELECT *=C2=A0 FROM a_src =C2=A0 UNION ALL =C2=A0 SELECT = * FROM b_src ; =C2=A0 David J. =C2=A0 =C2=A0 Your chaining version with WITH was the only one I could get = to work. I=20 think that is the cleanest version as it wraps the rather large query behin= d=20 the derived tables in a readable fashion. =C2=A0 Thanks! =C2=A0 -- Andreas = Jospeh Krogh=20 CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com=20 www.visena.com =20 =C2=A0 ------=_Part_712_950954365.1402608513493 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 torsdag 12. juni 2014 kl. 22:36:23, skrev David G Johnston <= ;david.g.johnston@gmail.com>:
[snip]
=C2=A0
=E2=80=8BWITH=C2=A0
a_src (companyid, projectid, a_count) AS ( SELECT companyid, COALESCE(pr= ojectid,'N/A'), a_count FROM company_a)
, b_src=E2=80=8B (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)
;
=C2=A0
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 combin= ations.
=C2=A0
The chaining version:
=C2=A0
=E2=80=8BWITH=C2=A0
a_src (companyid, projectid, a_count) AS ( SEL= ECT companyid, COALESCE(projectid,'N/A'), a_count FROM company_a)
, b_src=E2=80=8B (companyid, projectid, b_coun= t) AS ( SELECT companyid, COALESCE(projectid,'N/A'), b_count FROM company_b= )
, c_src=E2=80=8B (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 JO= IN c_src USING (companyid, projectid)) abc_src
;
=C2=A0
Though you could also test whether the following is faster:
=C2=A0
a_raw FULL JOIN b_raw ON ((a_raw.companyid, COALESCE(a_raw.projectid, 'N= /A')) =3D (b_raw.companyid, COALESCE(b_raw.projectid, 'N/A')))
=C2=A0
=C2=A0
=C2=A0
Alternatively...go vertical:
=C2=A0
WITH
a_src (companyid, projectid, item_count, item_= type) AS ( SELECT companyid, COALESCE(projectid,'N/A'), a_count, 'A' FROM c= ompany_a)
, b_src=E2=80=8B (companyid, projectid, item_c= ount, item_type) AS ( SELECT companyid, COALESCE(projectid,'N/A'), b_count,= 'B' FROM company_b)
=C2=A0
SELECT *=C2=A0
FROM a_src
=C2=A0
UNION ALL
=C2=A0
SELECT *
FROM b_src
;
=C2=A0
David J.
=C2=A0
=C2=A0
Your chaining version with WITH was the only one I could get to work.<= /div>
I think that is the cleanest version as it wraps the rather large quer= y behind the derived tables in a readable fashion.
=C2=A0
Thanks!
=C2=A0
=C2=A0
------=_Part_712_950954365.1402608513493-- ------=_Part_711_838159301.1402608513465 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_711_838159301.1402608513465-- ------=_Part_710_658337235.1402608513465--