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





Message-ID: <Pine.LNX.3.96.980803115843.3957A-100000@admin.nrnet.org>
Permalink:  ../Pine.LNX.3.96.980803115843.3957A-100000@admin.nrnet.org/
Also on:    postgresql.org/message-id/Pine.LNX.3.96.980803115843.3957A-100000@admin.nrnet.org

 · 

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 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