I've got a database that is coming from a Mumps design that, for
simplicity, let's say has two tables:
Invoice Table Diagnosis Table
----------------------------- -------------------------------
Inv# Dx1 Dx2 Dx3 Record# Diagnosis
123 1 2 1 Influenza
234 2 3 4 2 Bad Karma
345 3 1 3 Hypertension
etc etc
The invoice table has one to three diagnosis codes associated with each
invoice. The Dx1..Dx3 fields in the invoice table are foreign keys
on the record# field in the Diagnosis table.
Although the Invoice table could obviously be normalized, it's not.
Now my question:
How do I define a join so I can run a query asking the question "show me
all invoices that have had (Dx1 = Hypertension or Dx2 = Hypertension or
Dx3 = Hypertension)"?
If I use "Where invoice.dx1 = diagnosis.record# or invoice.dx2 =
diagnosis.record# or invoice.dx3 = diagnosis.record#" then I just get the
first matching record. I want ALL matches, so the report would look like:
Invoice
234
345
Is there any way around this problem short of normalizing the database? I
appreciate any ideas!
