Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WvAkF-0002DF-0x for pgsql-sql@arkaria.postgresql.org; Thu, 12 Jun 2014 19:33:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WvAkE-0006ow-8v for pgsql-sql@arkaria.postgresql.org; Thu, 12 Jun 2014 19:33:06 +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 1WvAkC-0006ok-W1 for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 19:33:05 +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 1WvAk9-0004r7-Uw for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 19:33:04 +0000 Received: from [10.47.1.10] (helo=tc7-on) by post.officenet.no with esmtp (Exim 4.76) (envelope-from ) id 1WvAk6-0001RB-So for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 21:33:00 +0200 Received: from localhost ([127.0.0.1] helo=tc7-on) by tc7-on with esmtp (Exim 4.76) (envelope-from ) id 1WvAjn-0005wD-Oy for pgsql-sql@postgresql.org; Thu, 12 Jun 2014 21:32:39 +0200 Date: Thu, 12 Jun 2014 21:32:39 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: <1402581759135-5807003.post@n5.nabble.com> 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_681_2043245047.1402601559122" 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_680_807264678.1402601559122 Content-Type: multipart/related; boundary="----=_Part_681_2043245047.1402601559122" ------=_Part_681_2043245047.1402601559122 Content-Type: multipart/alternative; boundary="----=_Part_682_615249427.1402601559155" ------=_Part_682_615249427.1402601559155 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 torsdag 12. juni 2014 kl. 16:02:39, skrev David G Johnston < david.g.johnston@gmail.com >: Note - num= bers=20 do not relate to your questions. This pretty much answers 1 and 3. 1. I'd ensure that project name/id on a table can never be null by creatin= g a dummy project that means "none assigned". 2. I would avoid full join.=C2=A0 To do so I'd cross join a distinct list = of companies with a distinct list of projects. 3. Against the join in 2 you then left join three times, once for each product table. 4. In the final result any product not having a correspond company/project would have its value coalesced to zero. The end result is a table without any NULL and, in the case of your explanatory data, 9 rows - two of which would be all zeros: (c3,p1) and (c3,p,2) If you cannot change the raw data I'd use CTE/WITH to normalize the data according to 1 and the use these CTEs in the rest of the query.=C2=A0 I wo= uld also do 2 is a CTE then 3 would be normal subqueries or relation reference= s as necessary. David J. =C2=A0 Hi and thanks for input. =C2=A0 I cannot change the data s= o I have to=20 deal with NULLs. =C2=A0 Not all companies or projects are in the result, on= ly the=20 ones involved having fruits, by combining the derived tables. I therefore d= on't=20 see how I effectively can build up a list of distinct companies and project= s to=20 join with. =C2=A0 I have a simplified version (full schema below) here whic= h shows=20 that there's an error in the query (when one extra derived table is added t= o=20 the query): =C2=A0 SELECT =C2=A0=C2=A0=C2=A0 coalesce(apl.company_name, bns.company_name, pns.compan= y_name,=20 prs.company_name) AS company_name, =C2=A0=C2=A0=C2=A0 coalesce(apl.project_name, bns.project_name, pns.projec= t_name,=20 prs.project_name) AS project_name, =C2=A0=C2=A0=C2=A0 apl.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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=20 num_apples, =C2=A0=C2=A0=C2=A0 bns.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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=20 num_bananas, =C2=A0=C2=A0=C2=A0 pns.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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=20 num_pineapples, =C2=A0=C2=A0=C2=A0 prs.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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=20 num_pears FROM =C2=A0 =C2=A0=C2=A0=C2=A0 company_apples AS apl =C2=A0 =C2= =A0=C2=A0=C2=A0 FULL OUTER JOIN=20 company_bananas AS bns =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=C2=A0 FULL OUTER JOI= N company_pineapples AS pns =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) =C2=A0 =C2=A0=C2=A0=C2=A0 FULL OUTER JOI= N company_pears AS prs =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 prs.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 prs.project_id IS NULL OR=20 apl.project_id =3D prs.project_id) =C2=A0 ORDER BY company_name ASC, projec= t_name ASC=20 NULLS FIRST; =C2=A0 =C2=A0 This gives the result: =C2=A0 # company_name pro= ject_name num_apples num_bananas num_pineapples num_pears 1 C1 NULL 2 2 10 NULL 2 C1 P1 5 12 NUL= L=20 NULL 3 C1 P2 2 NULL NULL NULL 4 C2 NULL 3 NULL 10 NULL 5 C2 P1 3 NULL NULL = NULL=20 6 C2 P2 3 NULL NULL NULL 7 C3 NULL NULL 8 NULL NULL 8 C3 NULL NULL NULL NUL= L 7=20 As you see, there are two rows for C3, which should have been 1, with=20 num_bananas=3D8 and num_pears=3D7. There has to be something wrong with my = FULL=20 OUTER JOINs but I don't know what.... =C2=A0 Anyone knows how to write the = correct=20 query for this? =C2=A0 Thanks. =C2=A0 =C2=A0 Full schame and example-data: = =C2=A0 drop table if=20 exists company_apples; drop table if exists company_bananas; drop table if exists company_pineapples; CREATE TABLE company_apples ( =C2=A0=C2=A0=C2=A0 company_id INT, =C2=A0=C2=A0=C2=A0 company_name VARCHAR, =C2=A0=C2=A0=C2=A0 project_id INT, =C2=A0=C2=A0=C2=A0 project_name VARCHAR, =C2=A0=C2=A0=C2=A0 qty BIGINT ); CREATE TABLE company_bananas ( =C2=A0=C2=A0=C2=A0 company_id INT, =C2=A0=C2=A0=C2=A0 company_name VARCHAR, =C2=A0=C2=A0=C2=A0 project_id INT, =C2=A0=C2=A0=C2=A0 project_name VARCHAR, =C2=A0=C2=A0=C2=A0 qty BIGINT ); CREATE TABLE company_pineapples ( =C2=A0=C2=A0=C2=A0 company_id INT, =C2=A0=C2=A0=C2=A0 company_name VARCHAR, =C2=A0=C2=A0=C2=A0 project_id INT, =C2=A0=C2=A0=C2=A0 project_name VARCHAR, =C2=A0=C2=A0=C2=A0 qty BIGINT ); CREATE TABLE company_pears ( =C2=A0=C2=A0=C2=A0 company_id INT, =C2=A0=C2=A0=C2=A0 company_name VARCHAR, =C2=A0=C2=A0=C2=A0 project_id INT, =C2=A0=C2=A0=C2=A0 project_name VARCHAR, =C2=A0=C2=A0=C2=A0 qty BIGINT );=20 INSERT INTO company_apples (company_id, company_name, project_id,=20 project_name, qty) VALUES (1, 'C1', null, null, 2); INSERT INTO company_apples (company_id, company_name, project_id,=20 project_name, qty) VALUES (1, 'C1', 1, 'P1', 5); INSERT INTO company_apples (company_id, company_name, project_id,=20 project_name, qty) VALUES (1, 'C1', 2, 'P2', 2); INSERT INTO company_apples (company_id, company_name, project_id,=20 project_name, qty) VALUES (2, 'C2', null, null, 3); INSERT INTO company_apples (company_id, company_name, project_id,=20 project_name, qty) VALUES (2, 'C2', 1, 'P1', 3); INSERT INTO company_apples (company_id, company_name, project_id,=20 project_name, qty) VALUES (2, 'C2', 2, 'P2', 3); INSERT INTO company_banana= s=20 (company_id, company_name, project_id, project_name, qty) VALUES (1, 'C1',= =20 null, null, 2); INSERT INTO company_bananas (company_id, company_name, project_id,=20 project_name, qty) VALUES (1, 'C1', 1, 'P1', 12); INSERT INTO company_bananas (company_id, company_name, project_id,=20 project_name, qty) VALUES (3, 'C3', null, null, 8); INSERT INTO=20 company_pineapples (company_id, company_name, project_id, project_name, qty= )=20 VALUES (1, 'C1', null, null, 10); INSERT INTO company_pineapples (company_id, company_name, project_id,=20 project_name, qty) VALUES (2, 'C2', null, null, 10); INSERT INTO company_pe= ars=20 (company_id, company_name, project_id, project_name, qty) VALUES (3, 'C3',= =20 null, null, 7); =C2=A0 select company_name, project_name, qty from company_= apples=20 order by company_name ASC, project_name ASC nulls first; select company_name, project_name, qty from company_bananas order by=20 company_name ASC, project_name ASC nulls first; select company_name, project_name, qty from company_pineapples order by=20 company_name ASC, project_name ASC nulls first; select company_name, project_name, qty from company_pears order by=20 company_name ASC, project_name ASC nulls first; -- Andreas Jospeh Krogh CTO= /=20 Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com=20 www.visena.com =20 =C2=A0 ------=_Part_682_615249427.1402601559155 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 torsdag 12. juni 2014 kl. 16:02:39, skrev David G Johnston <= ;david.g.johnston@gmail.com>:
Not= e - numbers do not relate to your questions. This pretty much answers 1
and 3.

1. I'd ensure that project name/id on a table can never be null by creating=
a dummy project that means "none assigned".
2. I would avoid full join.=C2=A0 To do so I'd cross join a distinct list o= f
companies with a distinct list of projects.
3. Against the join in 2 you then left join three times, once for each
product table.
4. In the final result any product not having a correspond company/project<= br> would have its value coalesced to zero.

The end result is a table without any NULL and, in the case of your
explanatory data, 9 rows - two of which would be all zeros: (c3,p1) and
(c3,p,2)

If you cannot change the raw data I'd use CTE/WITH to normalize the data according to 1 and the use these CTEs in the rest of the query.=C2=A0 I wou= ld
also do 2 is a CTE then 3 would be normal subqueries or relation references=
as necessary.

David J.
=C2=A0
Hi and thanks for input.
=C2=A0
I cannot change the data so I have to deal with NULLs.
=C2=A0
Not all companies or projects are in the result, only the ones involve= d having fruits, by combining the derived tables. I therefore don't see how= I effectively can build up a list of distinct companies and projects to jo= in with.
=C2=A0
I have a simplified version (full schema below) here which shows that = there's an error in the query (when one extra derived table is added to the= query):
=C2=A0
SELECT
=C2=A0=C2=A0=C2=A0 coalesce(apl.company_name, bns.company_name, pns.company= _name, prs.company_name) AS company_name,
=C2=A0=C2=A0=C2=A0 coalesce(apl.project_name, bns.project_name, pns.project= _name, prs.project_name) AS project_name,
=C2=A0=C2=A0=C2=A0 apl.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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 num_apples, =C2=A0=C2=A0=C2=A0 bns.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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 num_bananas, =C2=A0=C2=A0=C2=A0 pns.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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 num_pineapples= ,
=C2=A0=C2=A0=C2=A0 prs.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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=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 num_pears
FROM=
=C2=A0
=C2=A0=C2= =A0=C2=A0 company_apples AS apl
=C2=A0
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN company_bananas AS bns
=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=C2=A0 FULL OUTER JOIN company_pineapples AS pns
=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)
=C2=A0
=C2=A0=C2= =A0=C2=A0 FULL OUTER JOIN company_pears AS prs
=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 prs.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 prs.project_id IS NULL OR apl.pro= ject_id =3D prs.project_id)
=C2=A0
ORDER BY co= mpany_name ASC, project_name ASC NULLS FIRST;
=C2=A0
=C2=A0
This gives the result:
=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=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= plesnum_pears<= /font>
1C1 =09=09=09NULL =09=09=092210 =09=09=09NULL =09=09
2C1 =09=09=09P1 =09=09=09512 =09=09=09NULL =09=09=09NULL =09=09
3C1 =09=09=09P2 =09=09=092NULL =09=09=09NULL =09=09=09NULL =09=09
4C2 =09=09=09NULL =09=09=093NULL =09=09=0910 =09=09=09NULL =09=09
5C2 =09=09=09P1 =09=09=093NULL =09=09=09NULL =09=09=09NULL =09=09
6C2 =09=09=09P2 =09=09=093NULL =09=09=09NULL =09=09=09NULL =09=09
7C3NULLNULL8NULLNULL
8C3NULLNULLNULLNULL7
As you see, there are two rows for C3, which should have been 1, with = num_bananas=3D8 and num_pears=3D7. There has to be something wrong with my = FULL OUTER JOINs but I don't know what....
=C2=A0
Anyone knows how to write the correct query for this?
=C2=A0
Thanks.
=C2=A0
=C2=A0
Full schame and example-data:
=C2=A0
drop table = if exists company_apples;
drop table if exists company_bananas;
drop table if exists company_pineapples;
CREATE TABL= E company_apples
(
=C2=A0=C2=A0=C2=A0 company_id INT,
=C2=A0=C2=A0=C2=A0 company_name VARCHAR,
=C2=A0=C2=A0=C2=A0 project_id INT,
=C2=A0=C2=A0=C2=A0 project_name VARCHAR,
=C2=A0=C2=A0=C2=A0 qty BIGINT
);
CREATE TABL= E company_bananas
(
=C2=A0=C2=A0=C2=A0 company_id INT,
=C2=A0=C2=A0=C2=A0 company_name VARCHAR,
=C2=A0=C2=A0=C2=A0 project_id INT,
=C2=A0=C2=A0=C2=A0 project_name VARCHAR,
=C2=A0=C2=A0=C2=A0 qty BIGINT
);
CREATE TABL= E company_pineapples
(
=C2=A0=C2=A0=C2=A0 company_id INT,
=C2=A0=C2=A0=C2=A0 company_name VARCHAR,
=C2=A0=C2=A0=C2=A0 project_id INT,
=C2=A0=C2=A0=C2=A0 project_name VARCHAR,
=C2=A0=C2=A0=C2=A0 qty BIGINT
);
CREATE TABL= E company_pears
(
=C2=A0=C2=A0=C2=A0 company_id INT,
=C2=A0=C2=A0=C2=A0 company_name VARCHAR,
=C2=A0=C2=A0=C2=A0 project_id INT,
=C2=A0=C2=A0=C2=A0 project_name VARCHAR,
=C2=A0=C2=A0=C2=A0 qty BIGINT
);

INSERT INTO comp= any_apples (company_id, company_name, project_id, project_name, qty) VALUES= (1, 'C1', null, null, 2);
INSERT INTO company_apples (company_id, company_name, project_id, project_n= ame, qty) VALUES (1, 'C1', 1, 'P1', 5);
INSERT INTO company_apples (company_id, company_name, project_id, project_n= ame, qty) VALUES (1, 'C1', 2, 'P2', 2);
INSERT INTO company_apples (company_id, company_name, project_id, project_n= ame, qty) VALUES (2, 'C2', null, null, 3);
INSERT INTO company_apples (company_id, company_name, project_id, project_n= ame, qty) VALUES (2, 'C2', 1, 'P1', 3);
INSERT INTO company_apples (company_id, company_name, project_id, project_n= ame, qty) VALUES (2, 'C2', 2, 'P2', 3);
INSERT INTO= company_bananas (company_id, company_name, project_id, project_name, qty) = VALUES (1, 'C1', null, null, 2);
INSERT INTO company_bananas (company_id, company_name, project_id, project_= name, qty) VALUES (1, 'C1', 1, 'P1', 12);
INSERT INTO company_bananas (company_id, company_name, project_id, project_= name, qty) VALUES (3, 'C3', null, null, 8);
INSERT INTO= company_pineapples (company_id, company_name, project_id, project_name, qt= y) VALUES (1, 'C1', null, null, 10);
INSERT INTO company_pineapples (company_id, company_name, project_id, proje= ct_name, qty) VALUES (2, 'C2', null, null, 10);
INSERT INTO= company_pears (company_id, company_name, project_id, project_name, qty) VA= LUES (3, 'C3', null, null, 7);
=C2=A0
select comp= any_name, project_name, qty from company_apples order by company_name ASC, = project_name ASC nulls first;
select company_name, project_name, qty from company_bananas order by compan= y_name ASC, project_name ASC nulls first;
select company_name, project_name, qty from company_pineapples order by com= pany_name ASC, project_name ASC nulls first;
select company_name, project_name, qty from company_pears order by company_= name ASC, project_name ASC nulls first;
=C2=A0
------=_Part_682_615249427.1402601559155-- ------=_Part_681_2043245047.1402601559122 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_681_2043245047.1402601559122-- ------=_Part_680_807264678.1402601559122--