3 Replies Latest reply: May 3, 2017 6:11 PM by Rob Wunderlich RSS

    Big Table A has multiple links to Bigger Table B

    Justin Dallas

      Hello Everyone,

       

      I've to an issue where the scenario is as follows.

       

      We have two main tables, Orders and Company(s).  The Order table looks like so

       

      Order:
        ....
         %ShippingCompanyId
         %ConsigneeCompanyId
         %BillToCompanyId
         %SendingCompanyId
       ...
      

       

      The issue is that I have 4 Companies attached to a single Order, which clearly will lead to a looping nightmare.  I've thought of doing something like the following pseudocode

       

      Shippers:
      LEFT KEEP(Order)
      Qualify Shippers or something...
      LOAD companyId as %ShippingCompanyId,
      *
      Resident Company
      ;
      
      Consigners:
      LEFT KEEP(Order)
      Qualify Consigners or something...
      LOAD companyId as %ConsigneeCompanyId,
      *
      Resident Company
      ;
      

       

       

      That seems like it would work, but it doesn't pass the smell test and it looks like it could cause an eruption since I'm possibly QUADRUPLING the data needed for the company table.

       

      What is the standard way to handle tables that can't be LEFT JOINned to another table (like a small State table (Name,Abbrev)) but have multiple linkings to another table?

       

      Any help is greatly appreciated.