pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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







view thread (3+ messages)

Message-ID: <3B682E65.0001BD.93146@frodo.searchcanada.ca>
Permalink:  ../3B682E65.0001BD.93146@frodo.searchcanada.ca/
Also on:    postgresql.org/message-id/3B682E65.0001BD.93146@frodo.searchcanada.ca

 · 

reply

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