4 Replies Latest reply: Mar 12, 2012 4:54 PM by skr RSS

    Join Trouble/Help

    user398680
      Hello,
      I have a table "Orders" that has an order in it,
      the table has "To_vendorCode, To_VendorInitials" and "From_vendorCode, From_VendorInitials"
      _____________________
      | Orders              |
      -----------------------
      | OrderID             |
      | To_vendorCode       |
      | To_VendorInitials   |
      | From_vendorCode     |
      | From_VendorInitials |
      | ....                |
      _______________________
      There is another table "VendorInfo" that has the details of all the vendors
      __________________
      | VendorInfo     |
      ------------------
      | VendorCode     |
      | VendorInitials |
      | name           |
      | email          |
      | phonenumber    |
      | ......         |
      __________________
      If I want to return 1 row that has the order
      but I want to do a join that contains all the info for the "to" and "from" vendors.
      Whats the join to do this?
      Thanks
        • 1. Re: Join Trouble/Help
          skr
          Hi.

          Here's one way.
          --not tested
          
          SELECT
               *     
          FROM
               orders ord,
               vendorinfo vi1,
               vendorinfo vi2
          WHERE
               ord.from_vendorcode = vi1.vendorcode AND
               ord.from_vendorinitials = vi1.vendorinitials AND
               ord.to_vendorcode = vi2.vendorcode AND
               ord.to_vendorinitials = vi2.vendorinitials;
          Hope this helps.

          Regards
          • 2. Re: Join Trouble/Help
            skr
            Hi.

            Same example tested.
            WITH orders AS
            (
                 SELECT 1 OrderID, 10 To_vendorCode, 'ABC' To_VendorInitials,20 From_vendorCode, 'DEF' From_VendorInitials FROM dual
            ),
            vendorinfo AS
            (
                 SELECT 10 vendorcode, 'ABC' vendorinitials, 'name1' name FROM dual UNION
                 SELECT 20 vendorcode, 'DEF' vendorinitials, 'name2' name FROM dual
            )
            SELECT
                 ord.orderid,
                 ord.to_vendorcode,
                 ord.to_vendorinitials,
                 ord.from_vendorcode,
                 ord.from_vendorinitials,
                 vi1.name from_vendor_name,
                 vi2.name to_vendor_name
            FROM
                 orders ord,
                 vendorinfo vi1,
                 vendorinfo vi2
            WHERE
                 ord.from_vendorcode = vi1.vendorcode AND
                 ord.from_vendorinitials = vi1.vendorinitials AND
                 ord.to_vendorcode = vi2.vendorcode AND
                 ord.to_vendorinitials = vi2.vendorinitials;
            
            1     10     ABC     20     DEF     name2     name1
            Or
            WITH orders AS
            (
                 SELECT 1 OrderID, 10 To_vendorCode, 'ABC' To_VendorInitials,20 From_vendorCode, 'DEF' From_VendorInitials FROM dual
            ),
            vendorinfo AS
            (
                 SELECT 10 vendorcode, 'ABC' vendorinitials, 'name1' name FROM dual UNION
                 SELECT 20 vendorcode, 'DEF' vendorinitials, 'name2' name FROM dual
            )
            SELECT
                 ord.orderid,
                 ord.to_vendorcode,
                 ord.to_vendorinitials,
                 ord.from_vendorcode,
                 ord.from_vendorinitials,
                 vi1.name from_vendor_name,
                 vi2.name to_vendor_name
            FROM
                 orders ord
                 INNER JOIN vendorinfo vi1 ON
                 (
                      ord.from_vendorcode = vi1.vendorcode AND
                      ord.from_vendorinitials = vi1.vendorinitials
                 )
                 INNER JOIN vendorinfo vi2 ON
                 (
                      ord.to_vendorcode = vi2.vendorcode AND
                      ord.to_vendorinitials = vi2.vendorinitials
                 );
            
            1     10     ABC     20     DEF     name2     name1
            Hope this helps.

            Regards.
            • 3. Re: Join Trouble/Help
              user398680
              Hi,
              Thanks for the response,

              That is close but for some reason I am getting 4 records.

              Maybe I need to show more of the query, I was trying to keep it less complicated.
              SELECT proj.job_number,   
                       proj.project_title,   
                       UPPER(item.description) AS item_description,   
                       cnmt_all_view.accepted_by1,   
                       cnmt_all_view.accepted_by2,   
                       substr(to_char(cnmt_all_view.accepted_date1,'MM/DD/YYYY HH24:MI:SS'),0,10) as  accepted_date1,       
                       substr(to_char(cnmt_all_view.accepted_date2, 'MM/DD/YYYY HH24:MI:SS'),0,10) as  accepted_date2,
                       cnmt_all_view.bill_add_line_1,   
                       cnmt_all_view.bill_add_line_2,   
                       cnmt_all_view.bill_city,   
                       cnmt_all_view.bill_company,   
                       concat(' ',cnmt_all_view.bill_postal_code) as bill_postal_code,   
                       cnmt_all_view.bill_state,   
                       cnmt_all_view.bill_country,   
                       substr(to_char(cnmt_all_view.completion_date, 'MM/DD/YYYY HH24:MI:SS'),0,10) as  completion_date, 
                       cnmt_all_view.description,   
                       substr(to_char(cnmt_all_view.document_date, 'MM/DD/YYYY HH24:MI:SS'),0,10) as  document_date,
                       cnmt_all_view.document_number,   
                       cnmt_all_view.fob_via,   
                       cnmt_all_view.from_add_line_1,   
                       cnmt_all_view.from_add_line_2,   
                       cnmt_all_view.from_city,   
                       cnmt_all_view.from_company,   
                       concat(' ',cnmt_all_view.from_postal_code) as from_postal_code,   
                       cnmt_all_view.from_state,   
                       cnmt_all_view.from_country,  
                       cnmt_all_view.lump_cost,   
                       cnmt_all_view.lump_tax,   
                       cnmt_all_view.managers_name,   
                       cnmt_all_view.remarks,   
                       cnmt_all_view.ship_add_line_1,   
                       cnmt_all_view.ship_add_line_2,   
                       cnmt_all_view.ship_city,   
                       cnmt_all_view.ship_company,   
                       concat(' ',cnmt_all_view.ship_postal_code) as ship_postal_code,   
                       cnmt_all_view.ship_state,   
                       cnmt_all_view.ship_country,   
                       cnmt_all_view.terms,   
                       cnmt_all_view.total_cost,   
                       cnmt_all_view.to_add_line_1,   
                       cnmt_all_view.to_add_line_2,   
                       cnmt_all_view.to_city,   
                       cnmt_all_view.to_company,   
                       concat(' ',cnmt_all_view.to_postal_code) as to_postal_code,   
                       cnmt_all_view.to_state,   
                       cnmt_all_view.to_country,   
                       cnmt_all_view.master_key,   
                       vi1.office_phone,
                       CASE 
                         WHEN vi1.mobile_phone IS NOT NULL THEN vi1.mobile_phone
                      WHEN vi1.office_phone IS NOT NULL THEN vi1.office_phone
                    END AS to_contact_number,
                       vi1.fax,   
                       vi1.mail_address as from_email_address,
                       vi2.mail_address as to_email_address,
                       cnmt_all_view.unit_tax,   
                       cnmt_all_view.unit_cost,   
                       cnmt_all_view.freight    
                  FROM admuser.cnmt_all_view LEFT OUTER JOIN admuser.item ON cnmt_all_view.item_type = item.acronym,   
                       admuser.proj,   
                       admuser.vndt vi1,
                       admuser.vndt vi2  
                 WHERE ( cnmt_all_view.project_name = proj.project_name ) and  
                       ( cnmt_all_view.from_vendor = vi1.vendor_code ) and  
                       ( cnmt_all_view.from_vendor_ini = vi1.initials ) and 
                       ( cnmt_all_view.to_vendor = vi2.vendor_code ) and  
                       ( cnmt_all_view.to_vendor_ini = vi2.initials ) and    
                       ( ( cnmt_all_view.master_key = '4343ggegdg34gsdfg44' ) AND  
                       ( vi1.project_name = 'PRJ_CAV' ) )
              If you need more info let me know.
              Thanks
              • 4. Re: Join Trouble/Help
                skr
                Hi.

                You should check the information, probably there are 2 records in one of the other tables that you're joining, try with just one record and debug in every table, without the data in the tables is hard to know the exact problem.

                Or post some sample data that you've identified as problematic, please just the relevant columns.

                Regards.