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

[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.

--

Andreas Jospeh Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com