combine two interval tables into single result set

  • Hello,

    Looking for help combining two interval tables together.  The tables are pretty simple...

    Table 1:

    Table 2:

    I'd like to create a result having 4 columns -> PhoneNbr, Interval, CountA, CountB.  I'd like to join the tables on PhoneNbr and Interval if there is a match so that we get both counts (CountA and CountB) in the same row on a PhoneNbr/Interval match, but each table may or may not have the same PhoneNbr's and Intervals.

    I've been trying to use full outer joins to do this but my results are just not working.  It seem like it should be a simple thing, but I'm not a SQL expert and I'm hoping someone here will be able to help.

    Appreciate your help and input.

     

  • Forgot to mention...  I need all the records from both tables, but I'd like them combined into a single row IF there is a match on PhoneNbr and Interval.  Thanks.

  • This should do the trick

    /* First get all of the records from Table1, and the Table2 values if they exist */
    SELECT T1.PhoneNbr, T1.Interval, T1.CountA, T2.CountB
    FROM Table1 AS T1
    LEFT JOIN Table2 AS T2
    ON T1.PhoneNbr = T2.PhoneNbr
    AND T1.Interval = T2.Interval
    /* Now add all of the records from Table2 that do not exist in Table1 */
    UNION ALL
    SELECT T2.PhoneNbr, T2.Interval, NULL AS CountA, T2.CountB
    FROM Table2 AS T2
    WHERE NOT EXISTS (SELECT 1 FROM Table1 AS T1
    WHERE T1.PhoneNbr = T2.PhoneNbr
    AND T1.Interval = T2.Interval
    );
  • Yes, this is the exact usage case for a FULL OUTER JOIN:

    SELECT 
    COALESCE(t1.PhoneNbr, t2.PhoneNbr) AS PhoneNbr,
    COALESCE(t1.Interval, t2.Interval) AS Interval,
    t1.CountA, t2.CountB
    FROM [Table 1] t1
    FULL OUTER JOIN [Table 2] t2 ON t2.PhoneNbr = T1.PhoneNbr AND
    t2.Interval = t1.Interval
    ORDER BY 1, 2

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks for the response.  This worked perfectly.  I was a little concerned about performance when I expanded the date range, but the query ran fast even when evaluating tens of thousands of rows in each of the tables (not that this would necessarily be practical, but wanted to do some validation).

    Thanks again!

  • @scottpletcher - Thanks for the response.  I'll try this solution as well when I get a chance.  I wasn't familiar w/ the COALESCE function so I really appreciate your input and expanding my SQL knowledge.

Viewing 6 posts - 1 through 6 (of 6 total)

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