Re: Help: Flexible SQL SELECT !!
>Date: Tue, 17 Oct 1995 15:40:08 -0600 (MDT)
>From: CRAIG@CHEMISTRY.CHEM.UTAH.EDU
>X-Informix-List-Id: <list.7683>
>
>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;>>
Use 'HAVING COUNT(DISTINCT diag_code) = 3', as in:
CREATE TEMP TABLE pt_code (card_no INTEGER, diag_code CHAR(7));
INSERT INTO pt_code VALUES (10007, "303.500");
INSERT INTO pt_code VALUES (10007, "303.500");
INSERT INTO pt_code VALUES (10007, "303.500");
INSERT INTO pt_code VALUES (10007, "303.500");
INSERT INTO pt_code VALUES (10008, "303.500");
INSERT INTO pt_code VALUES (10008, "339.000");
INSERT INTO pt_code VALUES (10008, "621.100");
INSERT INTO pt_code VALUES (10008, "621.100");
SELECT card_no
FROM pt_code
WHERE diag_code IN ("303.500", "339.000", "621.100")
GROUP BY card_no
HAVING COUNT(DISTINCT diag_code) = 3;
This returns only 10008, as it should.
I also saw an answer which used a FOREACH loop and so on. I'd suggest that
a solution which involves fetching data from the the database, and then
sending some of the data back again, in order to formulate the answer (and
then fetching the final answer) will be much slower than a solution where
the database does all the work internally and simply sends back the result.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>