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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply