Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f71GTnf99029 for ; Wed, 1 Aug 2001 12:29:49 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id AE9D62174; Wed, 1 Aug 2001 12:29:25 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3B682E65.0001BD.93146@frodo.searchcanada.ca> To: tgl@sss.pgh.pa.us Subject: Re: Exclusion List Cc: pgsql-sql@postgresql.org From: "Michael Richards" X-Fastmail-IP: 24.43.130.241 Date: Wed, 1 Aug 2001 12:29:25 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200108/8 X-Sequence-Number: 4229 > "Michael Richards" 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 ; 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 ; Wed, 1 Aug 2001 12:44:16 -0500 (CDT) Message-ID: <001f01c11abe$ebd29740$8301a8c0@RICHARD> From: "Richard Rowell" To: 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