Received: from mail2.panix.com (mail2.panix.com [166.84.0.213]) by hub.org (8.8.8/8.7.5) with ESMTP id MAA11449 for ; Mon, 3 Aug 1998 12:53:44 -0400 (EDT) Received: from mailhost.nrnet.org (root@mailhost.nrnet.org [166.84.192.39]) by mail2.panix.com (8.8.8/8.8.8/PanixM1.3) with ESMTP id MAA25073; Mon, 3 Aug 1998 12:53:38 -0400 (EDT) Received: from q8.nrnet.org (uucp@localhost) by mailhost.nrnet.org (8.8.4/8.8.4) with UUCP id MAA17993; Mon, 3 Aug 1998 12:54:09 -0400 Received: (from uucp@localhost) by q8.nrnet.org (8.8.2/8.8.4) id QAA07543; Mon, 3 Aug 1998 16:46:46 GMT Received: from localhost (tomg@localhost) by admin.nrnet.org (8.8.7/8.8.7) with SMTP id MAA04125; Mon, 3 Aug 1998 12:52:41 -0400 Date: Mon, 3 Aug 1998 12:52:41 -0400 (EDT) From: Thomas Good Reply-To: Thomas Good To: Federico Passaro Cc: "Postgre[SQL] List" Subject: subqueries v. joins (locked my keys in the car) Message-ID: Mime-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 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