From: Thomas Good <tomg@admin.nrnet.org>
To: Federico Passaro <fede@link.it>
Cc: Postgre[SQL] List <pgsql-sql@postgreSQL.org>
Subject: subqueries v. joins (locked my keys in the car)
Date: Mon, 3 Aug 1998 12:52:41 -0400 (EDT)
Message-ID: <Pine.LNX.3.96.980803115843.3957A-100000@admin.nrnet.org> (raw)
Federico,
I tried the join you messaged me about:
SELECT C1.tr_id, C2.tr_date FROM crtrd1 C1, OUTER crtrd1 C2
WHERE C1.tr_unit = 'SMA' AND (C1.tr_type = 'T' OR C1.tr_type = 'O')
AND C2.tr_unit = 'SMA' AND (C2.tr_type = 'I' OR C2.tr_type = 'A')
AND C1.tr_id = C2.tr_id
AND C1.tr_date > C2.tr_date
ORDER BY tr_date DESC;
However, the output is further away from the target... :-(
So, I've been messing about with a subquery:
SELECT tr_id, tr_date FROM crtrd1
WHERE tr_unit = 'SMA' AND (tr_type = 'A' OR tr_type = 'I')
AND tr_id NOT IN
(SELECT tr_id FROM crtrd1
WHERE tr_unit = 'SMA' AND (tr_type = 'T' OR tr_type = 'O'))
ORDER BY tr_date DESC;
This is nearer the mark, except that:
if a patient gets admitted, discharged, then readmitted...he will
not get picked up by this query (I have such an individual)...
Alas I get back only 12 of the 13 `legit' customers.
And so I tried this here:
SELECT tr_id, tr_date FROM crtrd1
WHERE tr_unit = 'SMA' AND (tr_type = 'A' OR tr_type = 'I')
AND tr_date > tr_date IN
(SELECT tr_date FROM crtrd1
WHERE tr_unit = 'SMA' AND (tr_type = 'T' OR tr_type = 'O'))
ORDER BY tr_date DESC;
But postgres complains:
ERROR: There is no operator '>' for types 'date' and 'bool'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR
So, am I really having a problem with relational operators and data
types or is this just (more ;-) bad syntax?
---------- Sisters of Charity Medical Center ----------
Department of Psychiatry
----
Thomas Good <tomg@q8.nrnet.org>
Coordinator, North Richmond C.M.H.C. Information Systems
75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528
Staten Island, NY 10304 Fax: 718-354-5056
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tomg@admin.nrnet.org, fede@link.it
Subject: Re: subqueries v. joins (locked my keys in the car)
In-Reply-To: <Pine.LNX.3.96.980803115843.3957A-100000@admin.nrnet.org>
References: <Pine.LNX.3.96.980803115843.3957A-100000@admin.nrnet.org>
* 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