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(at)q8(dot)nrnet(dot)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