Post reply

Trying to join three different tables ...

  • May 4, 2007 at 7:25 am

    #118449

    Hello,

    I'm trying to join three different tables that have a program name and order no in common however the third criteria is what the users are calling milestone and in some cases the milestone can be in all three tables and sometimes its in only two or one table(s)

    How can I merge these three tables:  below is an example of what I'm trying to accomplish:

    Please disregard the second portion.  Thanks in advance

    program_nameOrder_numberrevenue_milestoneLabor HoursLabor Amount 
    LPD 23340232Engineering Support232.56.96888 
    LPD 23340232HOPM Materials00 
    LPD 23340232Program Management649.528.74807 
          
    program_nameOrder_number

    revenue_milestone

    		Materials  
    LPD 23340232HOPM Materials383.37953  
    LPD 23340232Port HOPM Assembly and Test19.53532  
    LPD 23340232Program Management0  
          

     

    Resultset should be      
    program_nameOrder_numberrevenue_milestoneLabor HoursLabor AmountMaterials
    LPD 23340232Engineering Support232.56.968880
    LPD 23340232HOPM Materials00383.37953
    LPD 23340232Program Management649.528.748070
    LPD 23340232Port HOPM Assembly and Test0019.53532

    program_nameOrder_numberrevenue_milestoneLabor HoursLabor Amount
    LPD 23340232Engineering Support232.56.96888
    LPD 23340232HOPM Materials00
    LPD 23340232Program Management649.528.74807
    program_nameOrder_numberrevenue_milestoneMaterials
    LPD 23340232HOPM Materials383.37953
    LPD 23340232Port HOPM Assembly and Test19.53532
    LPD 23340232Program Management0
    Resultset should be 
    program_nameOrder_numberrevenue_milestoneLabor HoursLabor AmountMaterials
    LPD 23340232Engineering Support232.56.968880
    LPD 23340232HOPM Materials00383.37953
    LPD 23340232Program Management649.528.748070
    LPD 23340232Port HOPM Assembly and Test0019.53532

  • May 4, 2007 at 8:05 am

    #704337

    Assuming program_name, Order_number, Revenue_milestone is unique for each table, maybe something like:

    SELECT D.program_name, D.Order_number, D.Revenue_milestone

        ,COALESCE(T1.[Labor Hours], 0) AS [Labor Hours]

        ,COALESCE(T1.[Labor Amount], 0) AS [Labor Amount]

        ,COALESCE(T2.Materials, 0) AS Materials

    FROM (

            SELECT T11.program_name, T11.Order_number, T11.Revenue_milestone

            FROM Table1 T11

            UNION

            SELECT T12.program_name, T12.Order_number, T12.Revenue_milestone

            FROM Table2 T12

        ) D

        LEFT JOIN Table1 T1

            ON D.program_name = T1.program_name

                AND D.Order_number = T1.Order_number

                AND D.Revenue_milestone = T1.Revenue_milestone

        LEFT JOIN Table2 T2

            ON D.program_name = T2.program_name

                AND D.Order_number = T2.Order_number

                AND D.Revenue_milestone = T2.Revenue_milestone

    ORDER BY D.program_name, D.Order_number, D.Revenue_milestone

  • May 4, 2007 at 8:22 am

    #704344

    Well here goes my first post, and i've only been working wils sql server for about a month, so This is probably wrong!!!  also the example only has 2 files but the post says 3 ??

     

    select program_name,Order_number,revenue_milestone,sum(Labor Hours),sum(Labor Amount),sum(Materials) from

    (select program_name,Order_number,revenue_milestone,Labor Hours,Labor Amount,0 as Materials from table1 UNION ALL select program_name,Order_number,revenue_milestone,Labor Hours,Labor Amount, Materials from table2) as a group by program_name,Order_number,revenue_milestone

     

  • May 4, 2007 at 8:27 am

    #704346

    PERFECT, works great

    Thank You

  • May 4, 2007 at 8:35 am

    #704347

    Scott's approach looks as thow it could be more efficient.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply