Re: Non-normalized Join Problem

John L Dunn <misioror_at_GAS.UUG.Arizona.EDU> wrote:

>What it doesn't solve is how to display a record showing all three
>diagnosis on the same line, i.e., given the following,
>> > 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
 

>display:
>
>Inv# Dx1 Dx2 Dx3
>123 Influenza Bad Karma
>234 Bad Karma Hypertension Diagnosis #4
>345 Hypertension Influenza
>
>THIS is the real problem.

Select a.inv#, ( select bb.diagnosis from diagnosis bb

                   where bb.record = a.dx1 ),
               ( select cc.diagnosis from diagnosis cc 
                   where cc.record = a.dx2),
               ( select dd.diagnosis from diagnosis dd 
                   where d.record  = a.dx3)
       from invoice a
       where a.inv# = 132 or a.inv# = 234 or a.inv# = 345;

Will do the job. The "Dx#" columns are foreign keys on the diagnosis table column "record", I hope.

-Austin Received on Fri Apr 05 1996 - 00:00:00 CEST