Re: Help: Flexible SQL SELECT !!
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;
The advantage of this approach is that it lends itself to queries such as
'Which card numbers have suffered two or more of the three diagnosis codes
[...]', because you change the HAVING condition to '>= 2'. Also, if the
list of diagnosis codes is in a table CodeTable, the query can be refined
further:
SELECT Card_no
FROM Pt_code
WHERE Diag_code IN (SELECT Diag_Code FROM CodeTable)
GROUP BY Card_no
HAVING COUNT(*) >= (SELECT COUNT(*) * 0.75 FROM CodeTable);
This condition is 'list the card numbers which have at least 75% of the
diagnosis codes listed in the CodeTable'.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
}From: proberts@lynx.informix.com (Paul Roberts)
}Date: 16 Oct 1995 22:33:05 GMT
}X-Informix-List-Id: <news.17953>
}
}Well, how about:-
}
}select *
} from pt_code
} where diag_code = "303.500"
} and card_no in ( select card_no
} from pt_code
} where diag_code = "339.000" )
} and card_no in ( select card_no
} from pt_code
} where diag_code = "621.100" )
}
}or something along those lines? Basically I am just getting the lists of
}all the card_nos for patients with each of the diagnoses you specify, then
}taking the intersection of those lists.
}
}- Paul
}
} In article <1995Oct16.141608.63895@cc.usu.edu>, <sl6gs@cc.usu.edu> wrote:
} >
} >I have a table of patient code, every patient with a card_no(unique)
} >but each card_no with multi DIAG_COD NO, I can not figure out
} >how to find a patient with several DIAG_COD at same time, say
} >I want to use SQL find all the patients with both DIAG_COD
} >621.100 and 303.500 or with 621.100 and 303.500 and 339.000
} >even more.
} >
} >sample table as following
} >
} >SQL> SELECT * FROM PT_CODE
} > 2 WHERE CARD_NO = '10007';
} >
} >Card_no WHEN C P DIAG_COD C REP_NO
} >----- --------- - - --------- - ---------
} >10007 17-MAR-89 0 T 621.100 A 0
} >10007 17-MAR-89 0 T 621.100 A 0
} >10007 11-MAR-88 0 T 621.100 B 0
} >10007 17-MAR-89 0 T 621.100 B 0
} >10007 04-SEP-87 0 T 621.100 A 0
} >10007 04-SEP-87 0 T 339.000 B 0
} >10007 15-JUL-87 0 T 621.100 C 0
} >10007 15-JUL-87 1 T 303.500 C 0
} >10007 11-MAR-88 0 T 621.100 A 0
} >10007 11-MAR-88 0 T 621.100 B 0
} >10007 21-SEP-92 0 T 621.100 A 0
} >10007 14-OCT-91 0 T 621.100 A 0
} >
} >Thanks in advance.
} >Wei JIang