Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wv1MJ-0000hJ-OH for pgsql-sql@arkaria.postgresql.org; Thu, 12 Jun 2014 09:31:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wv1MI-0004Ec-Qi for pgsql-sql@arkaria.postgresql.org; Thu, 12 Jun 2014 09:31:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Wv1MH-0004EV-J5 for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 09:31:45 +0000 Received: from post.officenet.no ([195.225.13.103]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Wv1MC-0001Xq-2r for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 09:31:44 +0000 Received: from [10.47.1.10] (helo=tc7-on) by post.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1Wv1M9-00069A-DK for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 11:31:39 +0200 Received: from localhost ([127.0.0.1] helo=tc7-on) by tc7-on with esmtp (Exim 4.76) (envelope-from ) id 1Wv1Lr-0005Up-3o for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 11:31:19 +0200 Date: Thu, 12 Jun 2014 11:31:18 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql Message-ID: In-Reply-To: Subject: 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-Forwarded-Message-Id: 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_546_1289701777.1402565478747" 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_545_617221141.1402565478747 Content-Type: multipart/related; boundary="----=_Part_546_1289701777.1402565478747" ------=_Part_546_1289701777.1402565478747 Content-Type: multipart/alternative; boundary="----=_Part_547_752245817.1402565478822" ------=_Part_547_752245817.1402565478822 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable (Sorry for posting again, but this time with more readable tables) =C2=A0 H= i all. =C2=A0=20 (complete schame with example-data as INSERT on bottom) =C2=A0 I have the n= eed to=20 show a report which is generated using 3 derived tables (sub-queries). For = the=20 sake of this example let's assume it a list off companies with projects and= the=20 quantity of fruit for each fruit in each company/project. A fruit can belon= g to=20 a company and optionally a project, and a project can be used with any comp= any.=20 =C2=A0 I have this data. =C2=A0 apples on each project/company: =C2=A0 # co= mp_name proj_name=20 sum 1 C1 NULL 2 2 C1 P1 5 3 C1 P2 2 4 C2 P1 3 5 C2 P2 3 =C2=A0 bananas on e= ach=20 project/company: =C2=A0 # comp_name proj_name sum 1 C1 NULL 2 2 C1 P1 12 3 = C3 NULL 8=20 =C2=A0 pineapples on each project/company: =C2=A0 # comp_name proj_name sum= 1 C1 NULL 10 2 C2 NULL 10 =C2=A0 I have this query but it produces lots of logically equiv= alent=20 rows (same company with no project repeated times, one for each fruit):=C2= =A0 select=20 apl.company_name as apl_company_name =C2=A0=C2=A0=C2=A0 , apl.project_name as apl_project_name =C2=A0=C2=A0=C2=A0 , apl.qty as num_apples =C2=A0=C2=A0=C2=A0 , bns.company_name as bns_company_name =C2=A0=C2=A0=C2=A0 , bns.project_name as bns_project_name =C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas =C2=A0=C2=A0=C2=A0 , pns.company_name as pns_company_name =C2=A0=C2=A0=C2=A0 , pns.project_name as pns_project_name =C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples FROM =C2=A0=C2=A0=C2=A0 ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, p= roj.name, sum(ap.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN co= mpany c ON ap.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left ou= ter join project proj ON ap.project_id =3D proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id =C2=A0= =C2=A0=C2=A0 ) as apl (company_id, company_name,=20 project_id, project_name, qty) =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(b.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from ban= ana b =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON b.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compa= ny_id AND apl.project_id =3D bns.project_id =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(pn.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pin= eapple pn =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.= company_id =3D pns.company_id AND apl.project_id =3D=20 pns.project_id order by coalesce(apl.company_name, bns.company_name,=20 pns.company_name) ASC =C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proj= ect_name) ASC=20 nulls first ; =C2=A0 # apl_company_name apl_project_name num_apples bns_company_name= =20 bns_project_name num_bananas pns_company_name pns_project_name num_pineappl= es 1=20 NULL NULL NULL C1 NULL 2 NULL NULL NULL 2 C1 NULL 2 NULL NULL NULL NULL NUL= L=20 NULL 3 NULL NULL NULL NULL NULL NULL C1 NULL 10 4 C1 P1 5 C1 P1 12 NULL NUL= L=20 NULL 5 C1 P2 2 NULL NULL NULL NULL NULL NULL 6 NULL NULL NULL NULL NULL NUL= L C2=20 NULL 10 7 C2 P1 3 NULL NULL NULL NULL NULL NULL 8 C2 P2 3 NULL NULL NULL NU= LL=20 NULL NULL 9 NULL NULL NULL C3 NULL 8 NULL NULL NULL =C2=A0 As you see in th= e above=20 result row 1, 2 and 3 all represent company C1 without project and with=20 apples=3D2, bananas=3D2, pineapples=3D10. =C2=A0 I'd like the output of a f= ull report to=20 look like: =C2=A0 # company_name project_name num_apples num_bananas num_pi= neapples 1 C1 NULL 2 2 10 2 C1 P1 5 12 NULL 3 C1 P2 2 NULL NULL 4 C2 NULL NULL NULL 10= 5 C2 P1 3 NULL NULL 6 C2 P2 3 NULL NULL 7 C3 NULL NULL 8 NULL =C2=A0 I get this = with this=20 query:=C2=A0 select coalesce(apl.company_name, bns.company_name, pns.compan= y_name)=20 as company_name =C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proj= ect_name) as=20 project_name =C2=A0=C2=A0=C2=A0 , apl.qty as num_apples =C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas =C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples FROM =C2=A0=C2=A0=C2=A0 ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, p= roj.name, sum(ap.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN co= mpany c ON ap.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left ou= ter join project proj ON ap.project_id =3D proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id =C2=A0= =C2=A0=C2=A0 ) as apl (company_id, company_name,=20 project_id, project_name, qty) =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(b.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from ban= ana b =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON b.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compa= ny_id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (apl.proj= ect_id IS NULL AND bns.project_id IS NULL OR=20 apl.project_id =3D bns.project_id ) =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(pn.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pin= eapple pn =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.= company_id =3D pns.company_id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 AND (apl.project_id IS NULL AND pns.project_id IS NULL OR=20 apl.project_id =3D pns.project_id) order by company_name ASC, project_name = ASC=20 nulls first ; =C2=A0 I have some questions:=20 * Is FULL OUTER JOIN the correct way to handle this kind of report-query?= =20 * Is the JOIN-clause correct?=20 * Is there a better way to avoid logically duplicate rows then the AND in = the=20 ON-clause for each FULL OUTER JOIN?=20 * Is there a way to avoid having to use coalesce to list the project and= =20 company-names? PS: Are questions like these better suited for StackOverflow= or=20 should I post them here? =C2=A0 Thanks. =C2=A0 =C2=A0 Here is the complete = SQL for the example: =C2=A0 drop table if exists pineapple; drop table if exists banana; drop table if exists apple; drop table if exists project; drop table if exists company; create table company( =C2=A0=C2=A0=C2=A0 id integer primary key, =C2=A0=C2=A0=C2=A0 name varchar not null unique ); create table project( =C2=A0=C2=A0=C2=A0 id integer primary key, =C2=A0=C2=A0=C2=A0 name varchar not null unique ); create table apple( =C2=A0=C2=A0=C2=A0 id serial primary key, =C2=A0=C2=A0=C2=A0 qty integer not null, =C2=A0=C2=A0=C2=A0 company_id integer NOT NULL references company(id), =C2=A0=C2=A0=C2=A0 project_id integer references project(id) ); create table banana( =C2=A0=C2=A0=C2=A0 id serial primary key, =C2=A0=C2=A0=C2=A0 qty integer not null, =C2=A0=C2=A0=C2=A0 company_id integer NOT NULL references company(id), =C2=A0=C2=A0=C2=A0 project_id integer references project(id) ); create table pineapple( =C2=A0=C2=A0=C2=A0 id serial primary key, =C2=A0=C2=A0=C2=A0 qty integer not null, =C2=A0=C2=A0=C2=A0 company_id integer NOT NULL references company(id), =C2=A0=C2=A0=C2=A0 project_id integer references project(id) );=20 -- Company1 insert into company(id, name) values(1, 'C1'); insert into project(id, name) values(1, 'P1'); insert into project(id, name) values(2, 'P2'); insert into apple(qty,=20 company_id) values(2, 1); insert into apple(qty, company_id, project_id) values(3, 1, 1); insert into apple(qty, company_id, project_id) values(2, 1, 1); insert into apple(qty, company_id, project_id) values(2, 1, 2); insert int= o=20 banana(qty, company_id) values(2, 1); insert into banana(qty, company_id, project_id) values(6, 1, 1); insert into banana(qty, company_id, project_id) values(6, 1, 1); insert in= to=20 pineapple(qty, company_id) values(10, 1);=20 -- Company2 insert into company(id, name) values(2, 'C2'); insert into project(id, name) values(3, 'P3'); insert into project(id, name) values(4, 'P4'); insert into apple(qty,=20 company_id, project_id) values(3, 2, 1); insert into apple(qty, company_id, project_id) values(3, 2, 2); insert int= o=20 pineapple(qty, company_id) values(10, 2);=20 -- Company3 insert into company(id, name) values(3, 'C3'); insert into project(id, name) values(5, 'P5'); insert into banana(qty, company_id) values(8, 3);=20 -- List all apples for projects and compaies select c.name as comp_name, proj.name as proj_name, sum(ap.qty) from apple ap =C2=A0=C2=A0=C2=A0 JOIN company c ON ap.company_id =3D c.id =C2=A0=C2=A0=C2=A0 left outer join project proj ON ap.project_id =3D proj.= id group by c.id, proj.id order by comp_name ASC, proj_name ASC nulls first;=20 -- List all bananas for projects and compaies select c.name as comp_name, proj.name as proj_name, sum(b.qty) from banana b =C2=A0=C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id =C2=A0=C2=A0=C2=A0 left outer join project proj ON b.project_id =3D proj.i= d group by c.id, proj.id order by comp_name ASC, proj_name ASC nulls first;=20 -- List all pineapples for projects and compaies select c.name as comp_name, proj.name as proj_name, sum(pn.qty) from pineapple pn =C2=A0=C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id =C2=A0=C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D proj.= id group by c.id, proj.id order by comp_name ASC, proj_name ASC nulls first;=20 -- Try to list all. Result is kind of correct but has many logically duplic= ate=20 rows, -- and on version of "company-name" and "project-name" for all 3 fruits select apl.company_name as apl_company_name =C2=A0=C2=A0=C2=A0 , apl.project_name as apl_project_name =C2=A0=C2=A0=C2=A0 , apl.qty as num_apples =C2=A0=C2=A0=C2=A0 , bns.company_name as bns_company_name =C2=A0=C2=A0=C2=A0 , bns.project_name as bns_project_name =C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas =C2=A0=C2=A0=C2=A0 , pns.company_name as pns_company_name =C2=A0=C2=A0=C2=A0 , pns.project_name as pns_project_name =C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples FROM =C2=A0=C2=A0=C2=A0 ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, p= roj.name, sum(ap.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN co= mpany c ON ap.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left ou= ter join project proj ON ap.project_id =3D proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id =C2=A0= =C2=A0=C2=A0 ) as apl (company_id, company_name,=20 project_id, project_name, qty) =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(b.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from ban= ana b =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON b.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compa= ny_id AND apl.project_id =3D bns.project_id =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(pn.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pin= eapple pn =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.= company_id =3D pns.company_id AND apl.project_id =3D=20 pns.project_id order by coalesce(apl.company_name, bns.company_name,=20 pns.company_name) ASC =C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proj= ect_name) ASC=20 nulls first ; =C2=A0 -- Try filtering out NULLs, seems to work but unsure if it's safe -- Is there a way to avoid coalescing the name-columns? select coalesce(apl.company_name, bns.company_name, pns.company_name) as= =20 company_name =C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proj= ect_name) as=20 project_name =C2=A0=C2=A0=C2=A0 , apl.qty as num_apples =C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas =C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples FROM =C2=A0=C2=A0=C2=A0 ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, p= roj.name, sum(ap.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN co= mpany c ON ap.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left ou= ter join project proj ON ap.project_id =3D proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id =C2=A0= =C2=A0=C2=A0 ) as apl (company_id, company_name,=20 project_id, project_name, qty) =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(b.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from ban= ana b =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON b.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compa= ny_id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (apl.proj= ect_id IS NULL AND bns.project_id IS NULL OR=20 apl.project_id =3D bns.project_id ) =C2=A0=C2=A0=C2=A0 FULL OUTER JOIN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c= .id, c.name, proj.id, proj.name, sum(pn.qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pin= eapple pn =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D=20 proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by= c.id, proj.id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns=20 (company_id, company_name, project_id, project_name, qty) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.= company_id =3D pns.company_id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 AND (apl.project_id IS NULL AND pns.project_id IS NULL OR=20 apl.project_id =3D pns.project_id) order by company_name ASC, project_name = ASC=20 nulls first ; =C2=A0 =C2=A0 -- Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: = +47 909 56 963 andreas@visena.com www.visena.com=20 ------=_Part_547_752245817.1402565478822 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
(Sorry for posting again, but this time with more readable tables)
=C2=A0
Hi all.
=C2=A0
(complete schame with example-data as INSERT on bottom)
=C2=A0
I have the need to show a report which is generated using 3 derived ta= bles (sub-queries). For the sake of this example let's assume it a list off= companies with projects and the quantity of fruit for each fruit in each c= ompany/project. A fruit can belong to a company and optionally a project, a= nd a project can be used with any company.
=C2=A0
I have this data.
=C2=A0
apples on each project/company:
=C2=A0
=09=09 =09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09
#comp_name<= /font>proj_name<= /font>sum=
1C1 =09=09=09NULL =09=09=092
2C1 =09=09=09P1 =09=09=095
3C1 =09=09=09P2 =09=09=092
4C2 =09=09=09P1 =09=09=093
5C2 =09=09=09P2 =09=09=093
=C2=A0
bananas on each project/company:
=C2=A0
=09=09 =09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09
#comp_name<= /font>proj_name<= /font>sum=
1C1 =09=09=09NULL =09=09=092
2C1 =09=09=09P1 =09=09=0912 =09=09
3C3 =09=09=09NULL =09=09=098
=C2=A0
pineapples on each project/company:
=C2=A0
=09=09 =09=09 =09=09 =09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09
#comp_name<= /font>proj_name<= /font>sum=
1C1 =09=09=09NULL =09=09=0910 =09=09
2C2 =09=09=09NULL =09=09=0910 =09=09
=C2=A0
I have this query but it produces lots of logically equivalent rows (s= ame company with no project repeated times, one for each fruit):
=C2=A0
select apl.= company_name as apl_company_name
=C2=A0=C2=A0=C2=A0 , apl.project_name as apl_project_name
=C2=A0=C2=A0=C2=A0 , apl.qty as num_apples
=C2=A0=C2=A0=C2=A0 , bns.company_name as bns_company_name
=C2=A0=C2=A0=C2=A0 , bns.project_name as bns_project_name
=C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas
=C2=A0=C2=A0=C2=A0 , pns.company_name as pns_company_name
=C2=A0=C2=A0=C2=A0 , pns.project_name as pns_project_name
=C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples
FROM
=C2=A0=C2=A0=C2=A0 (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, pr= oj.name, sum(ap.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN com= pany c ON ap.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left out= er join project proj ON ap.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id
=C2=A0=C2= =A0=C2=A0 ) as apl (company_id, company_name, project_id, project_name, qty= )
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(b.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from banana= b
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON b.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compan= y_id AND apl.project_id =3D bns.project_id
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(pn.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pineap= ple pn
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.c= ompany_id =3D pns.company_id AND apl.project_id =3D pns.project_id
order by co= alesce(apl.company_name, bns.company_name, pns.company_name) ASC
=C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proje= ct_name) ASC nulls first
;
=C2=A0
=09=09 =09=09 =09=09 =09=09 =09=09 =09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09
#apl_compan= y_nameapl_projec= t_namenum_apples= bns_compan= y_namebns_projec= t_namenum_banana= spns_compan= y_namepns_projec= t_namenum_pineap= ples
1NULL =09=09=09NULL =09=09=09NULL =09=09=09C1 =09=09=09NULL =09=09=092NULL =09=09=09NULL =09=09=09NULL =09=09
2C1 =09=09=09NULL =09=09=092NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09
3NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09C1 =09=09=09NULL =09=09=0910 =09=09
4C1 =09=09=09P1 =09=09=095C1 =09=09=09P1 =09=09=0912 =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09
5C1 =09=09=09P2 =09=09=092NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09
6NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09C2 =09=09=09NULL =09=09=0910 =09=09
7C2 =09=09=09P1 =09=09=093NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09
8C2 =09=09=09P2 =09=09=093NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09
9NULL =09=09=09NULL =09=09=09NULL =09=09=09C3 =09=09=09NULL =09=09=098NULL =09=09=09NULL =09=09=09NULL =09=09
=C2=A0
As you see in the above result row 1, 2 and 3 all represent company C1= without project and with apples=3D2, bananas=3D2, pineapples=3D10.
=C2=A0
I'd like the output of a full report to look like:
=C2=A0
=09=09 =09=09 =09=09 =09=09 =09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09
#company_na= meproject_na= menum_apples= num_banana= snum_pineap= ples
1C1 =09=09=09NULL =09=09=092210 =09=09
2C1 =09=09=09P1 =09=09=09512 =09=09=09NULL =09=09
3C1 =09=09=09P2 =09=09=092NULL =09=09=09NULL =09=09
4C2 =09=09=09NULL =09=09=09NULL =09=09=09NULL =09=09=0910 =09=09
5C2 =09=09=09P1 =09=09=093NULL =09=09=09NULL =09=09
6C2 =09=09=09P2 =09=09=093NULL =09=09=09NULL =09=09
7C3 =09=09=09NULL =09=09=09NULL =09=09=098NULL =09=09
=C2=A0
I get this with this query:
=C2=A0
select coalesce(apl.company_name, bns.company_name, pns.company_name) as compa= ny_name
=C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, p= ns.project_name) as project_name
=C2=A0=C2=A0=C2=A0 , apl.qty as num_apples
=C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas
=C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples
FROM
=C2=A0=C2=A0=C2=A0 (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, pr= oj.name, sum(ap.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN com= pany c ON ap.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left out= er join project proj ON ap.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id
=C2=A0=C2= =A0=C2=A0 ) as apl (company_id, company_name, project_id, project_name, qty= )
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(b.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from banana= b
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON b.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compan= y_id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (a= pl.project_id IS NULL AND bns.project_id IS NULL OR apl.project_id =3D bns.= project_id )
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(pn.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pineap= ple pn
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.c= ompany_id =3D pns.company_id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 AND (apl.project_id IS NULL AND pns.project_id IS NULL OR= apl.project_id =3D pns.project_id)
order by co= mpany_name ASC, project_name ASC nulls first
;
=C2=A0
I have some questions:
    =09
  1. Is FULL OUTER JOIN the correct way to handle this kind of report-que= ry?
    2. =09
  2. Is the JOIN-clause correct?
    3. =09
  3. Is there a better way to avoid logically duplicate rows then the AND= in the ON-clause for each FULL OUTER JOIN?
    4. =09
  4. Is there a way to avoid having to use coalesce to list the project a= nd company-names?
PS: Are questions like these better suited for StackOverflow or should= I post them here?
=C2=A0
Thanks.
=C2=A0
=C2=A0
Here is the complete SQL for the example:
=C2=A0
drop table = if exists pineapple;
drop table if exists banana;
drop table if exists apple;
drop table if exists project;
drop table if exists company;
create tabl= e company(
=C2=A0=C2=A0=C2=A0 id integer primary key,
=C2=A0=C2=A0=C2=A0 name varchar not null unique
);
create tabl= e project(
=C2=A0=C2=A0=C2=A0 id integer primary key,
=C2=A0=C2=A0=C2=A0 name varchar not null unique
);
create tabl= e apple(
=C2=A0=C2=A0=C2=A0 id serial primary key,
=C2=A0=C2=A0=C2=A0 qty integer not null,
=C2=A0=C2=A0=C2=A0 company_id integer NOT NULL references company(id),
=C2=A0=C2=A0=C2=A0 project_id integer references project(id)
);
create tabl= e banana(
=C2=A0=C2=A0=C2=A0 id serial primary key,
=C2=A0=C2=A0=C2=A0 qty integer not null,
=C2=A0=C2=A0=C2=A0 company_id integer NOT NULL references company(id),
=C2=A0=C2=A0=C2=A0 project_id integer references project(id)
);
create tabl= e pineapple(
=C2=A0=C2=A0=C2=A0 id serial primary key,
=C2=A0=C2=A0=C2=A0 qty integer not null,
=C2=A0=C2=A0=C2=A0 company_id integer NOT NULL references company(id),
=C2=A0=C2=A0=C2=A0 project_id integer references project(id)
);

-- Company1
insert into company(id, name) values(1, 'C1');
insert into project(id, name) values(1, 'P1');
insert into project(id, name) values(2, 'P2');
insert into= apple(qty, company_id) values(2, 1);
insert into apple(qty, company_id, project_id) values(3, 1, 1);
insert into apple(qty, company_id, project_id) values(2, 1, 1);
insert into apple(qty, company_id, project_id) values(2, 1, 2);
insert into= banana(qty, company_id) values(2, 1);
insert into banana(qty, company_id, project_id) values(6, 1, 1);
insert into banana(qty, company_id, project_id) values(6, 1, 1);
insert into= pineapple(qty, company_id) values(10, 1);

-- Company2
insert into company(id, name) values(2, 'C2');
insert into project(id, name) values(3, 'P3');
insert into project(id, name) values(4, 'P4');
insert into= apple(qty, company_id, project_id) values(3, 2, 1);
insert into apple(qty, company_id, project_id) values(3, 2, 2);
insert into= pineapple(qty, company_id) values(10, 2);

-- Company3
insert into company(id, name) values(3, 'C3');
insert into project(id, name) values(5, 'P5');
insert into banana(qty, company_id) values(8, 3);

-- List all appl= es for projects and compaies
select c.name as comp_name, proj.name as proj_name, sum(ap.qty)
from apple ap
=C2=A0=C2=A0=C2=A0 JOIN company c ON ap.company_id =3D c.id
=C2=A0=C2=A0=C2=A0 left outer join project proj ON ap.project_id =3D proj.i= d
group by c.id, proj.id
order by comp_name ASC, proj_name ASC nulls first;

-- List all bana= nas for projects and compaies
select c.name as comp_name, proj.name as proj_name, sum(b.qty)
from banana b
=C2=A0=C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id
=C2=A0=C2=A0=C2=A0 left outer join project proj ON b.project_id =3D proj.id=
group by c.id, proj.id
order by comp_name ASC, proj_name ASC nulls first;

-- List all pine= apples for projects and compaies
select c.name as comp_name, proj.name as proj_name, sum(pn.qty)
from pineapple pn
=C2=A0=C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id
=C2=A0=C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D proj.i= d
group by c.id, proj.id
order by comp_name ASC, proj_name ASC nulls first;

-- Try to list a= ll. Result is kind of correct but has many logically duplicate rows,
-- and on version of "company-name" and "project-name" = for all 3 fruits
select apl.company_name as apl_company_name
=C2=A0=C2=A0=C2=A0 , apl.project_name as apl_project_name
=C2=A0=C2=A0=C2=A0 , apl.qty as num_apples
=C2=A0=C2=A0=C2=A0 , bns.company_name as bns_company_name
=C2=A0=C2=A0=C2=A0 , bns.project_name as bns_project_name
=C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas
=C2=A0=C2=A0=C2=A0 , pns.company_name as pns_company_name
=C2=A0=C2=A0=C2=A0 , pns.project_name as pns_project_name
=C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples
FROM
=C2=A0=C2=A0=C2=A0 (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, pr= oj.name, sum(ap.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN com= pany c ON ap.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left out= er join project proj ON ap.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id
=C2=A0=C2= =A0=C2=A0 ) as apl (company_id, company_name, project_id, project_name, qty= )
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(b.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from banana= b
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON b.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compan= y_id AND apl.project_id =3D bns.project_id
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(pn.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pineap= ple pn
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.c= ompany_id =3D pns.company_id AND apl.project_id =3D pns.project_id
order by co= alesce(apl.company_name, bns.company_name, pns.company_name) ASC
=C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proje= ct_name) ASC nulls first
;
=C2=A0
-- Try filt= ering out NULLs, seems to work but unsure if it's safe
-- Is there a way to avoid coalescing the name-columns?
select coalesce(apl.company_name, bns.company_name, pns.company_name) as co= mpany_name
=C2=A0=C2=A0=C2=A0 , coalesce(apl.project_name, bns.project_name, pns.proje= ct_name) as project_name
=C2=A0=C2=A0=C2=A0 , apl.qty as num_apples
=C2=A0=C2=A0=C2=A0 , bns.qty as num_bananas
=C2=A0=C2=A0=C2=A0 , pns.qty as num_pineapples
FROM
=C2=A0=C2=A0=C2=A0 (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id, c.name, proj.id, pr= oj.name, sum(ap.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from apple ap
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 JOIN com= pany c ON ap.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 left out= er join project proj ON ap.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.id, proj.id
=C2=A0=C2= =A0=C2=A0 ) as apl (company_id, company_name, project_id, project_name, qty= )
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(b.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from banana= b
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON b.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON b.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as bns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.company_id =3D bns.compan= y_id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (apl.proje= ct_id IS NULL AND bns.project_id IS NULL OR apl.project_id =3D bns.project_= id )
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select c.id= , c.name, proj.id, proj.name, sum(pn.qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from pineap= ple pn
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 JOIN company c ON pn.company_id =3D c.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 left outer join project proj ON pn.project_id =3D proj.id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 group by c.= id, proj.id
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) as pns (company_id, company_name, project_= id, project_name, qty)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON apl.c= ompany_id =3D pns.company_id
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 AND (apl.project_id IS NULL AND pns.project_id IS NULL OR apl.pro= ject_id =3D pns.project_id)
order by co= mpany_name ASC, project_name ASC nulls first
;
=C2=A0
=C2=A0
--
Andrea= s Jospeh Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
3D""
------=_Part_547_752245817.1402565478822-- ------=_Part_546_1289701777.1402565478747 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_546_1289701777.1402565478747-- ------=_Part_545_617221141.1402565478747--