Re: Non-normalized Join Problem

Austin Moseley <\"moseba_at_audv55.aud.alcatel.com'> wrote:
>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;
>

No. Wrong.

Sorry, Got ahead of myself.  

If you insist on nulls in your invoice table, then

Should be :

select distinct inv,diag1,diag2,diag3 from (

Select a.inv, decode(a.dx1,NULL, ' ',b.diag) diag1,
              decode(a.dx2,NULL, ' ',c.diag) diag2,
              decode(a.dx3,NULL, ' ',d.diag) diag3 from
 invoice a, diagnosis b, diagnosis c, diagnosis d where
              ( a.dx1 = b.rec and a.dx2 = c.rec and a.dx3 = d.rec) or
              ( a.dx1 = b.rec and a.dx2 = c.rec and a.dx3 is null) or 
              ( a.dx1 = b.rec and a.dx2 is null and a.dx3 = d.rec) or 
              ( a.dx1 is null and a.dx2 = c.rec and a.dx3 = d.rec) or
              ( a.dx1 is null and a.dx2 is null and a.dx3 = d.rec) or 
              ( a.dx1 is null and a.dx2 = c.rec and a.dx3 is null) or
              ( a.dx1 = b.rec and a.dx2 is null and a.dx3 is null) or
              ( a.dx1 is null and a.dx2 is null and a.dx3 is null)

It works. I checked it. :)

Else, if you have a blank diag line in your diag table with a corresponding record of 0, then:

select a.inv, b.diag, c.diag, d.diag from

                   invoice a, diagnosis b,diagnosis c, diagnosis d 
      where a.dx1 = b.record and a.dx2 = c.record and a.dx3 = d.record;

-Austin Received on Sat Apr 06 1996 - 00:00:00 CEST