Received: from xserver.server (barco2.comcasttel.net [206.135.253.162]) by hub.org (8.10.1/8.10.1) with ESMTP id e6RDFaA91791 for ; Thu, 27 Jul 2000 09:15:40 -0400 (EDT) Received: by XSERVER with Internet Mail Service (5.5.2650.21) id ; Thu, 27 Jul 2000 09:16:42 -0400 Message-ID: From: Henry Lafleur To: pgsql-sql@fc.emc.com.ph, pgsql-sql@postgresql.org, reedstrm@rice.edu Subject: RE: Re(2): optimize sql Date: Thu, 27 Jul 2000 09:15:17 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200007/254 If you know that 't' will always be the highest character in the active field for all records: SELECT name FROM office, office_application WHERE code = office_code GROUP BY name HAVING MAX(active) < 't' Of course, if you have an active that is 'z' for example, then this won't work. I think this should work also regardless of max(active) for the table: SELECT name FROM office, office_application WHERE code = office_code AND active <= 't' GROUP BY name HAVING MAX(active) < 't' UNION SELECT name FROM office, office_application WHERE code = office_code AND active >= 't' GROUP BY name HAVING MIN(active) > 't' Henry -----Original Message----- From: pgsql-sql@fc.emc.com.ph [mailto:pgsql-sql@fc.emc.com.ph] Sent: Wednesday, July 26, 2000 9:40 PM To: pgsql-sql@postgresql.org; reedstrm@rice.edu Subject: Re(2): [SQL] optimize sql reedstrm@rice.edu writes: >How does the output of the above differ from: > >SELECT name FROM office, office_application >WHERE code = office_code >AND active != 't'; > >Without knowing the table structures (which tables to active, code, >and office_code belong to?) it's hard to suggest much else. > >Ross The name and code fields belong to office table. While office_code and active fields belong to office_application table. The name field have duplicates and among the duplicates, only one active field is TRUE. I just wanted to get name field that has no TRUE active field. Any other idea? Thanks. sherwin