From: Michael Richards <michael@fastmail.ca>
To: tgl@sss.pgh.pa.us
Cc: pgsql-sql@postgresql.org
Subject: Re: Exclusion List
Date: Wed, 1 Aug 2001 12:29:25 -0400 (EDT)
Message-ID: <3B682E65.0001BD.93146@frodo.searchcanada.ca> (raw)
> "Michael Richards" <michael@fastmail.ca> writes:
>> The reduction of the list doesn't seem to be terribly efficient.
>> Here are some strategies I've been looking at:
>
>> select id from users WHERE
>> id not in (select userid from sentletters where lettertype=1) AND
>> aclgroup IN (1,2);
>
> Try an EXCEPT, along the lines of
>
> (select id from users where conditions) except
> (select userid from sentletters where other-conditions);
I tried except but in this case I'd have to use this to extract the
list of ids and then re-join it with users again to get the rest of
the data from the users table :(
-Michael
_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians
From pgsql-sql-owner@postgresql.org Wed Aug 1 13:20:26 2001
Received: from www.bowmansystems.com (www.bowmansystems.com [63.72.114.143])
by postgresql.org (8.11.3/8.11.1) with ESMTP id f71HKMf15423
for <pgsql-sql@postgresql.org>; Wed, 1 Aug 2001 13:20:22 -0400 (EDT)
(envelope-from richard@bowmansystems.com)
Received: from RICHARD ([63.72.114.222])
by www.bowmansystems.com (8.9.0/8.9.0) with SMTP id MAA23918
for <pgsql-sql@postgresql.org>; Wed, 1 Aug 2001 12:44:16 -0500 (CDT)
Message-ID: <001f01c11abe$ebd29740$8301a8c0@RICHARD>
From: "Richard Rowell" <richard@bowmansystems.com>
To: <pgsql-sql@postgresql.org>
Subject: Outer Join Syntax
Date: Wed, 1 Aug 2001 12:19:14 -0700
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
X-Archive-Number: 200108/9
X-Sequence-Number: 4230
I'm doing a feasability study on porting our flagship product to Postgres
(from MS_SQL). I have run across a few snags, the largest of which is the
outer join syntax. MS has some nice syntactical sugar with the *=/=*
operators that Postgres dosen't seem to support. I am confused on how
to replicate the behavior however. We often link together many tables via
outer joins to form a view such as:
SELECT Assessment_medical_id, a.Readonly_agency, a.Date_added, ag.name as
'AgencyName',
y1.Yesno_descrip as 'healthprob', y2.Yesno_descrip as
'MentalIllness', y3.Yesno_descrip as 'MentalTreatment',
y4.Yesno_descrip as 'drugabuse', d1.Drug_abuse_type_descrip
as 'drug1', d2.Drug_abuse_type_descrip as 'drug2',
d3.Drug_abuse_type_descrip as 'drug3',
d4.Drug_abuse_type_descrip as 'drug4', d5.Drug_abuse_type_descrip as
'drug5'
FROM ASSESSMENT_MEDICAL a, AGENCIES ag, YESNO_TYPES02 y1,
YESNO_TYPES02 y2, YESNO_TYPES02 y3, YESNO_TYPES02 y4,
DRUG_ABUSE_TYPES d1, DRUG_ABUSE_TYPES d2, DRUG_ABUSE_TYPES
d3, DRUG_ABUSE_TYPES d4, DRUG_ABUSE_TYPES d5
WHERE a.inactive != 'Y'
AND a.Client_id = $Edit_Client_id
AND a.Agency_creating *= ag.Agency_id
AND a.Health_prob *= y1.Yesno_code
AND a.EmoMental_illness *= y2.Yesno_code
AND a.Treatment_for_emomental *= y3.Yesno_code
AND a.AlchoholDrug_abuse *= y4.Yesno_code
AND a.AlchoholDrug_abuse_type1 *= d1.Drug_abuse_type_id
AND a.AlchoholDrug_abuse_type2 *= d2.Drug_abuse_type_id
AND a.AlchoholDrug_abuse_type3 *= d3.Drug_abuse_type_id
AND a.AlchoholDrug_abuse_type4 *= d4.Drug_abuse_type_id
AND a.AlchoholDrug_abuse_type5 *= d5.Drug_abuse_type_id
I'm just not grasping how one would accomplish the same using the SQL-92
syntax.
TIA
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael@fastmail.ca, tgl@sss.pgh.pa.us
Subject: Re: Exclusion List
In-Reply-To: <3B682E65.0001BD.93146@frodo.searchcanada.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox