Re: Non-normalized Join Problem

From: Sanjay D.S. <sanjay_at_fsg.prusec.com>
Date: 1996/03/28
Message-ID: <4jerht$1g7_at_prufire4.prusec.com>#1/1


John L Dunn <misioror_at_gas.uug.arizona.edu> wrote:
>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

select * from inv,diag where dx1 = dx and dx = 3 union
select * from inv,diag where dx2 = dx and dx = 3 union
select * from inv,diag where dx3 = dx and dx = 3 /

hope this works for you.

Sanjay D.S.
Oracle Consultant
Prudential Securities, Inc. Received on Thu Mar 28 1996 - 00:00:00 CET

Original text of this message