Re: Help: Flexible SQL SELECT !!
chemistry.chem.utah.edu!CRAIG@uunet.uu.net 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;
* >
I presented a similiar version of this select statement a while ago dealing
with a two table join this is what I got (they are converted to show your
variables):
--Q1--
SELECT UNIQUE card_no FROM pt_code P1
WHERE EXISTS(SELECT * FROM pt_code P2
WHERE P2.card_no = P1.card_no AND P2.diag_code = '303.500')
AND EXISTS(SELECT * FROM pt_code P2
WHERE P2.card_no = P1.card_no AND P2.diag_code = '339.000')
AND EXISTS(SELECT * FROM pt_code P2
WHERE P2.card_no = P1.card_no AND P2.diag_code = '621.100')
--Q2--
SELECT UNIQUE card_no FROM pt_code P1
WHERE 3 = ( SELECT COUNT(*) FROM pt_code P2
WHERE P2.card_no = P1.card_no AND
P2.diag_code IN ('303.500', '339.000', '621.100')
)
--Q3--
SELECT UNIQUE card_no
FROM pt_code P1, pt_code P2, pt_code P3, pt_code P4
WHERE P1.card_no = P2.card_no AND P2.diag_code = '303.500'
AND P1.card_no = P3.card_no AND P3.diag_code = '339.000'
AND P1.card_no = P4.card_no AND P4.diag_code = '621.100'
--Q4--
SELECT card_no
FROM pt_code
WHERE card_no IN
(SELECT card_no
FROM pt_code P2
WHERE P2.diag_code IN ('303.500', '339.000', '621.100')
GROUP BY P2.card_no
HAVING COUNT(*) = 3
);
Obviously, Q4 and Q2 will not do it for you if you allow duplicates of
card_no combined with diag_code in the table.
Hope this helps,
Robert Minter Data Systems Support \\\\\\_///
Senior Software Engineer A Client Technologies Company ( _ _ )
E-Mail: rob@dssmktg.com Tel: 714.771.0454 (| ^ |)
#include <disclaimer.h> Fax: 714.771.3028 \\`-'/
De Colores - Emmaus OC-13 SURF'S UP \\_/