På torsdag 12. juni 2014 kl. 22:36:23, skrev David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)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(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>