Re: Non-normalized Join Problem

From: Hartlieb WILD <whsysana_at_ping.at>
Date: 1996/03/27
Message-ID: <N.032796.213835.32_at_ping.at>#1/1

Dear John,
first of all I cannot see, why your solution "Where invoice.dx1 = diagnosis.record# or invoice.dx2 = diagnosis.record# or invoice.dx3 = diagnosis.record#" then gets the first matching record only. But maybe this could help:

SELECT <what you want>
FROM Invoice_Table IT, Diagnosis_Table DT WHERE INSTR (CONCAT (IT.Dx1,IT.Dx2,IT.Dx3, ...), DT.record#) > 0

INSTR returns the position of the search-string (hiere: DT.record#) and returns 0 if not found.
The solution above works if you know the record-number of the Diagnosis.

---

A second solution might go with an INTESECT and a UNION:

SELECT IT.inv#, DT.record#
FROM Invoice_Table IT, Diagnosis_Table DT WHERE DT.disgnostics = '<what you choose>' INTERSECT
( SELECT IT.inv#, IT.dx1
  FROM Invoice_Table IT
  UNION
  SELECT IT.inv#, IT.dx2
  FROM Invoice_Table IT
  UNION
  SELECT IT.inv#, IT.dx3
  FROM Invoice_Table IT )

The first SELECT produces a set of all invoices kombined with the chosen diagnosis. The UNIONed SELECTS form a set of actually stored invoices, kombined with the diagnosis. To reduce the number of records in the UNIONed SELECTS you should add a WHERE clause "WHERE IT.dx1 IS NOT NULL".

The INTERSECT should leave the desired rows as remainder.

I could not test this, so please tell me if my suggested solutions work as expected.

Thenk you,
sincerely
H. WILD

+==========================================================================+
| Hartlieb  WILD        Systemanalyse    Programmierung     EDV-Training   |
|                     allgemein beeideter gerichtlicher Sachverständiger   |
|                                                                          |
| A-6073 SISTRANS 280   e-mail:  whsysana_at_ping.at     Tel.: (++43) (0)512  |
| Austria               CompuServe:   100131,1552                 378 110  |
+==========================================================================+

> 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!
>
> TIA,
>
> John
Received on Wed Mar 27 1996 - 00:00:00 CET