Re: Help: Flexible SQL SELECT !!
CRAIG@CHEMISTRY.CHEM.UTAH.EDU writes:
->
->John,
->
-> I, too, was thinking of your "having count..." approach. The problem
->though is that in the example table a patient might have the same
->diagnosis code more than once. So, your SQL would also find a patient
->which has 3 or more "303.500" codes but no "339.000" or "621.100"
->code. Can you think of a way to avoid this problem?
->
-> --Craig (craig@chemistry.utah.edu)
->
->>
->> Or, as another way of doing the same job:
->>
->> SELECT card_no
->> FROM pt_code
->> WHERE diag_code IN ("303.500", "339.000", "621.100")
->> GROUP BY card_no
->> HAVING COUNT(*) = 3;
How about simply:
SELECT unique card_no, diag_code
FROM pt_code
WHERE diag_code IN ("303.500", "339.000", "621.100")
into temp t1 with no log;
SELECT card_no
FROM t1
WHERE diag_code IN ("303.500", "339.000", "621.100")
GROUP BY card_no
HAVING COUNT(*) = 3;
Regards,
- Cathy
--------------------------------------------------------------------------------
Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (970) 491-1294
Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205