Matching on null?

I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one o
f
the columns being null. For instance, here's one of the problem rows from th
e
transactions...
item id shelf owner group bank sum
15214 NULL 300 220 MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214 NULL 300 220 MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))|||"Mike C#" wrote:

> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury