Path: utzoo!attcan!uunet!seismo!sundc!pitstop!sun!decwrl!labrea!rutgers!att!ihlpb!skennedy
From: skennedy@ihlpb.ATT.COM (S. Kennedy)
Newsgroups: comp.databases
Subject: One to Many to One to One relationships in Informix SQL  (LONG!!)
Message-ID: <9176@ihlpb.ATT.COM>
Date: 11 Dec 88 00:25:06 GMT
Reply-To: skennedy@ihlpb.UUCP (S. Kennedy)
Distribution: na
Organization: AT&T Bell Laboratories - Naperville, Illinois
Lines: 129


Hello!

I got a problem and just cannot figure out how to do it.


I am trying to create a select that will do a one to many to one to one 
relationship.


SCENARIO
========


I have four tables that I need to group.

   Table A            Table B             Table C           Table D
   -------            -------             -------           -------

   leadsource         leadsource         leadsource     
   location           location                              location
   type               type
   leadnumber       
                      rundate
                      cost
                                         description
                                                            description


Tables A, C and D will have a one to one match as Tables C & D are simply
description tables for codes in Table A.

Leadsource, location, and type make up a unique filed.  There are many locations
for the same leadsource and several types for one location.a (Maybe too
relational??) The real kicker comes when Table B may have several entrys for 
the same leadsource, location and type but with unique rundates.

The following:


select 
      A.*, B.*, C.*
      from C, outer (A, outer B)
      where C.leadsource = A.leadsource
      and C.leadsource = B.leadsource
      and A.leadsource = B.leadsource
      and A.location = B.location
      and A.type = B.type
      into temp temp01;

select 
      temp01.*, D.*
      from temp01, outer D
      where A.location = D.location
      order by C.leadsource, D.location, A.type, A.rundate, A.leadnumber


yeilds me a select that only gets me an entry if there is a matching one in
A and if there are two B entries (different rundate), it gets the same A pair
for each B.

                        Value 1           Value 2        Value 3     Value 4
                        -------           -------        -------     -------
  A.leadsource           aaa               aaa            zzzz
  B.leadsource           aaa               aaa             
  C.leadsource           aaa               aaa            zzzz         ttt	
  A.type                   s                 s               r
  B.type                   s                 s               
  B.location              a1                a1
  A.location              a1                a1              a1
  D.location              a1                a1              a1
  D.description         (desc)             (desc)         (desc)
  C.description         (desc)             (desc)         (desc)      (desc)
  B.rundate            01/01/88           01/01/87      
  B.cost                1000.00            300.00              
  A.leadnumber           100               100



  Value 1 & 2
  -----------

  For the same leadsource (aaa), location (a1), type (s) and leadnumber 100, 
  there are two different costs (1000, 300). Therefore, I received an entry
  twice for the same lead. (Twice as many forms selected.)

  Value 3
  -------

  No cost assocaited with that leadsource. No problem.


  Value 4
  -------

  No leadsource in table A to associate with table B or C. Looses the form from
  table B.




  SOLUTION??
  ========

  To be able to only get :

    1) as many forms as it needs to do this. 
    2) a form from table B when there is no matching form from table A.
    3) Only as many duplicates as necessary and a way to process and dispose
       of the duplicates.

   How??  I just can't figure out how to arrange the select statement to fix 
  this.


  Sorry this is so long. Please e-mail any possible solutions to

                      att!ihlpb!skennedy 
                      Shawn Kennedy
                      AT&T Bell Labs
  
  Thanks to all that repliy in advancve, 



  Shawn Kennedy                                         AT&T Bell Labs
  att!ihlpb!skennedy                                    skennedy@ihlpb!att.com
  1 312 979 2494