JOINS with COALESCE

  • Hi All,

    Please fine the following is the scenario:

    CREATE TABLE #ABC

    (

    id INT IDENTITY(1,1),

    anotherid INT

    )

    CREATE TABLE #DEF

    (

    id INT IDENTITY(1,1),

    anotherid INT

    )

    INSERT INTO #ABC

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10

    INSERT INTO #DEF

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 10

    SELECT '#ABC',A.*,'#DEF',B.* FROM #ABC A

    LEFT JOIN #DEF B ON A.anotherid = COALESCE(B.anotherid,B.anotherid+1,B.anotherid+2)

    Please check the following OUTPUT:

    (No column name)idanotherid(No column name)idanotherid

    #ABC 1 1 #DEF 1 1

    #ABC 2 2 #DEF 2 2

    #ABC 3 3 #DEF NULL NULL

    #ABC 4 4 #DEF NULL NULL

    #ABC 5 5 #DEF NULL NULL

    #ABC 6 6 #DEF 3 6

    #ABC 7 7 #DEF 4 7

    #ABC 8 8 #DEF 5 8

    #ABC 9 9 #DEF NULL NULL

    #ABC 10 10 #DEF 6 10

    I need to output like this:

    Instead of NULL i need the next number in the another id column.(6)

    (No column name)idanotherid(No column name)idanotherid

    #ABC 1 1 #DEF 1 1

    #ABC 2 2 #DEF 2 2

    #ABC 3 3 #DEF 3 6

    #ABC 4 4 #DEF 3 6

    #ABC 5 5 #DEF 3 6

    #ABC 6 6 #DEF 3 6

    #ABC 7 7 #DEF 4 7

    #ABC 8 8 #DEF 5 8

    #ABC 9 9 #DEF 7 10

    #ABC 10 10 #DEF 7 10

    Please let me know if this is not understandable!!

    Thanks

    Deepak

  • Based on your sample data, this would work, but it relies on #DEF.anotherid being unique and inserted into the table in numerical order - it's not a particularly nice piece of T-SQL!

    SELECT

    '#ABC',

    A.*,

    '#DEF',

    MIN(b.id) id,

    MIN(b.anotherid) anotherid FROM #ABC A

    LEFT OUTER JOIN #DEF B ON A.anotherid <= B.[anotherid]

    GROUP BY a.id, a.[anotherid]

    If you didn't need to see #DEF.id in your results then you could drop the column from your select list and this code should work regardless of the order #DEF.anotherid is inserted in.

    Just out of curiosity, what is it you are trying to do?

    Cheers,

    Simon 🙂

  • Your issue is that, in the ON clause B.anotherid is never null so you are never getting to anotherid + 1 or anotherid + 2 as the evaluation is only returning a true or false.

    Simon's solution is good.

Viewing 3 posts - 1 through 2 (of 2 total)

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