Thread: Re(2): 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
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
but active is a boolean field. HLafleur@phoenixforge.com writes: >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
I'm kind of new to pgsql, but as long as MAX works for boolean fields, they you can just change the first query below with: HAVING MAX(active) != 't' but it seems that pgsql can have user defined aggregates, so you could define a function that computes the MAX of a boolean and define true to be the max. Henry -----Original Message----- From: pgsql-sql@fc.emc.com.ph [mailto:pgsql-sql@fc.emc.com.ph] Sent: Thursday, July 27, 2000 9:28 PM To: HLafleur@phoenixforge.com; pgsql-sql@postgresql.org Subject: Re: RE: Re(2): [SQL] optimize sql but active is a boolean field. HLafleur@phoenixforge.com writes: >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