return results ignoring nulls

  • I am joining 3 tables in SQL. There should always be a AccountID for ProductID1, but not necessarily AccountIDs for ProductID2 or ProductID3.

    SELECT

    i.AccountID AS [ProductID1],

    o.AccountID AS [ProductID2],

    a.AccountID AS [ProductID3]

    FROM [dbo].[Product1] i

    LEFT OUTER JOIN [dbo].[Product2] o

    ON i.ProductAccountID = o.AccountID

    LEFT OUTER JOIN [dbo].[Product3] a

    ON i.ProductAccountID = a.AccountID

    My results return like this.

    Product1Product2Product3

    35 NULL NULL

    35 NULL 290

    35 659 NULL

    14 NULL NULL

    14 NULL 296

    40 NULL NULL

    I want it to return like this.

    Product1Product2Product3

    35 659 290

    14 NULL 296

    40 NULL NULL

    How can I do this?

  • Your data doesn't cover all possible cases, so it's not clear which approach to use. What do you want to happen with the following data:

    Product1Product2Product3

    35NULLNULL

    35NULL290

    35NULL300

    35450NULL

    35500350

    35659NULL

    14NULLNULL

    14NULL296

    40 NULL NULL

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • maybe...

    SELECT P1, MAX(P2) AS MP2, MAX(P3) AS MP3

    FROM

    (SELECT 35 AS P1,NULL AS P2,NULL AS P3

    UNION ALL SELECT 35,NULL,290

    UNION ALL SELECT 35, 659,NULL

    UNION ALL SELECT 14,NULL,NULL

    UNION ALL SELECT 14,NULL,296

    UNION ALL SELECT 40, NULL, NULL) x

    GROUP BY P1

    ORDER BY P1;

  • There will never be more than 1 AccountID for any of the products. For example you have with Product1 35 it could only have Product 2 of 450 or NULL and Product 3 290 or NULL.

    Product1Product2Product3

    35NULLNULL

    35NULL290

    35NULL300

    35450NULL

    35500350

    35659NULL

    14NULLNULL

    14NULL296

    40 NULL NULL

  • Where's the ProductID in your dataset? Either my psychic powers or my eyesight must be failing.

    How did my solution not work?

  • Piet's on the right track. Try the following:

    SELECT

    i.AccountID AS [ProductID1],

    MAX(o.AccountID) AS [ProductID2],

    MAX(a.AccountID) AS [ProductID3]

    FROM [Product1] i

    LEFT OUTER JOIN [Product2] o

    ON i.ProductAccountID = o.AccountID

    LEFT OUTER JOIN [Product3] a

    ON i.ProductAccountID = a.AccountID

    GROUP BY i.AccountID

    It does seem strange that you have 3 separate product tables.

    Drew

    PS: You're more likely to get better, quicker responses if you follow the Forum Etiquette: How to post data/code on a forum to get the best help[/url]

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • It did work. Thank you

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

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