Re: Exclusion List

> "Michael Richards" <michael(at)fastmail(dot)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(at)postgresql(dot)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(at)postgresql(dot)org>; Wed, 1 Aug 2001 13:20:22 -0400 (EDT)
(envelope-from richard(at)bowmansystems(dot)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(at)postgresql(dot)org>; Wed, 1 Aug 2001 12:44:16 -0500 (CDT)
Message-ID: <001f01c11abe$ebd29740$8301a8c0(at)RICHARD>
From: "Richard Rowell" <richard(at)bowmansystems(dot)com>
To: <pgsql-sql(at)postgresql(dot)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