Replace NULL values created by left outer join

  • I am trying to replace the NULL values that are created by a left outer join but having a little trouble figuring out the best way to do that. Here is the situation:

    Table 1:

    rowid

    product

    date

    resellerid

    Table 2:

    reseller id

    reseller_name

    I have a query that looks like this:

    select 1.product, 1.date, 2.reseller_name

    into product_reseller

    from table_1 as 1 left outer join table_2 as 2

    on 1.resellerid = 2.resellerid

    group by 1.product, 1.date, 2.reseller_name

    The problem is that in some cases there is a reseller ID in table 1 that doesn't match any resellers in table 2 (I know this shouldn't happen in the first place). Instead of the left outer join putting NULL into the new product_reseller table in this situation I want it to put "Other".

    The actual tables are a bit more complicated than what I have outlined here, but I think this makes it a bit easier to understand. In table_2 there are multiple resellerid that have the reseller_name = "Other" so I want to group these all together with the NULL values that the left outer join creates.

    Any help would be greatly appreciated!

  • Lookup COALESCE and ISNUL in Books Online:

    SELECT coalesce(1.resellername, 'Other') As resellername, ...

    FROM ...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • None of the fields are NULL in the two source tables. When resellerid from table_1 doesn't match any resellerid in table_2 it is resulting in a NULL reseller name being brought back by the outer join.

    I could go through and update every NULL value in the resulting table with "other" and then re-run the grouping again. I am trying to not make this a 3 step process though.

    It needs to be something like IF no match then put "Other" instead of NULL.

  • Which is exactly what COALESCE or ISNULL will do for you. Sorry, mis-quoted and it should be:

    SELECT coalesce(2.resellername, 'Other')

    The above can be re-written using a case expression, if you want:

    SELECT Case When 2.resellername IS NULL Then 'Other' Else 2.resellername End

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • .... and to add to the suggestion to use COALESCE, you can in fact use the NULL values to look for rows in Table 1 for which there is no match in Table 2 by adding this:

    WHERE 2.resellerid IS NULL

    Rich

  • That worked great Jeffery! Thanks for your help!

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

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