Received: from romana.hajhouse.org ([207.215.68.169]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1C17NE90817 for ; Mon, 11 Feb 2002 20:07:23 -0500 (EST) (envelope-from hajhouse@houseag.com) Received: from wotan ([192.168.0.2]) by romana.hajhouse.org with esmtp (Exim 3.33 #1 (Debian)) id 16aMeE-0001Nz-00 for ; Mon, 11 Feb 2002 12:01:58 -0800 Received: from hajhouse by wotan with local (Exim 3.33 #1 (Debian)) id 16aMhZ-0004TL-00 for ; Mon, 11 Feb 2002 12:05:25 -0800 Date: Mon, 11 Feb 2002 12:05:24 -0800 To: pgsql-sql@postgresql.org Subject: UNION and rows improperly unified: query optimization question Message-ID: <20020211200524.GA17170@wotan> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="n8g4imXOkfNTN/H1" Content-Disposition: inline User-Agent: Mutt/1.3.25i From: Henry House X-Archive-Number: 200202/120 X-Sequence-Number: 6548 --n8g4imXOkfNTN/H1 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings. I have an accounting system in Postgres which has tables 'transact' and 'gl_entry'. Each check, deposit, etc has one entry in transact, but there may be multiple entries in gl_entry if one check was for multiple expenses that are tracked separately (for example, one check to AT= &T might cover both telephone and Internet service, which are two different costing categories). Also, debit and credit are supposed to appear in seperate columns, even though they are one number with either positive or negative sign in the table. So I run two select and combine them using a union statement, but this improperly combines two gl_entry lines th= at do not differ in amount or transaction ID. My solution is to select also the unique ide number from gl_entry and remove it by wrapping the SELECT...UNION...SELECT in another SELECT. This seems awfully ugly. Is there a better way? Here is the query that I use now, which produces correct results: SELECT postdate, person, debit, credit, descr, num FROM ( SELECT t.postdate, t.person, abs(g.amt) AS debit, '0.00' AS credit, t.des= cr, t.num, g.id AS gid FROM transact t, gl_entry g ' + WHERE t.id =3D g.transact_id AND g.amt >=3D 0 UNION SELECT t.postdate, t.person, '0.00' AS debit, abs(g.amt) AS credit, t.des= cr, t.num, g.id AS gid FROM transact t, gl_entry g WHERE t.id =3D g.transact_id AND g.amt < 0 ) AS subselect ORDER BY postdate I would like to get rid of the outer SELECT, if possible. --=20 Henry House The attached file is a digital signature. See for information. My OpenPGP key: . --n8g4imXOkfNTN/H1 Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8aCQEKK1cAVjXujwRAnKdAKDSfQJTo5Ng/pSFZV9z/vAjEHSnswCgkwmh fNL2w2K1wM6QZMTi4NP7O24= =oqx9 -----END PGP SIGNATURE----- --n8g4imXOkfNTN/H1--