February 19, 2011 at 1:28 pm
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!
February 19, 2011 at 2:03 pm
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
February 19, 2011 at 2:28 pm
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.
February 19, 2011 at 2:37 pm
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
February 20, 2011 at 10:17 am
.... 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
February 20, 2011 at 11:07 am
That worked great Jeffery! Thanks for your help!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy