Re: What is the best way to do this query?

Craig wrote:

I was thinking there must be an easier way to do this.
Any ideas?

Two ways come to mind. Untested.

Way 1: Criteria table

Create a criteria table :priv:critcodes with one column clinicalcodes.

Put the choices in there. Get the number of choices (somenumber)

select c."status", c."Year", c."unique#", b.HospitalNumber, b.FirstName,
b.LastName, b.BirthDate, b.Sex, m.AdmitDateTime, count(*) numcodes
from ":ktables:Clininfo.db" c,
":ktables:Bioinfo.DB" b,
":ktables:MedInfo.DB" m,
":priv:crit.db" crit
where c.status = b.status
and c."Year" = b."Year"
and c."Unique#" = b."Unique#"
and c.status = m.status
and c."Year" = m."Year"
and c."Unique#" = m."Unique#"
and c.clinicalcode = crit.clinicalcode
group by c."status", c."year", c."unique#", b."HospitalNumber",
b."FirstName",
b."LastName", b."BirthDate", b."Sex", m."AdmitDateTime"
having count(*) > ~somenumber

~somenumber doesn't have to be the number of criteria they've entered. You
could use this to do a search to see who's had, say, any 5 of the 9 codes
they enter, or whatever.

Way 2: Without Criteria table

select c."status", c."Year", c."unique#", b.HospitalNumber, b.FirstName,
b.LastName, b.BirthDate, b.Sex, m.AdmitDateTime, count(*) numcodes
from ":ktables:Clininfo.db" c,
":ktables:Bioinfo.DB" b,
":ktables:MedInfo.DB" m
where c.status = b.status
and c."Year" = b."Year"
and c."Unique#" = b."Unique#"
and c.status = m.status
and c."Year" = m."Year"
and c."Unique#" = m."Unique#"
and c.clinicalcode in ( a list of codes you assemble )
group by c."status", c."year", c."unique#", b."HospitalNumber",
b."FirstName",
b."LastName", b."BirthDate", b."Sex", m."AdmitDateTime"
having count(*) > ~somenumber

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

.