subqueries v. joins (locked my keys in the car)

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