SUM () from a multipart JOIN

Question

SUM () from a multipart JOIN

SELECT SUM(t.OptLevel) + SUM(o.ReqLevel1) + SUM(b.nOptValue) FROM _Inventory AS i 
        LEFT JOIN _Items AS t ON t.ID64 = i.ItemID
        LEFT JOIN _RefObjCommon AS o ON o.ID = t.RefItemID
        LEFT OUTER JOIN _BindingOptionWithItem AS b ON b.nItemDBID = i.ItemID 
        WHERE i.CharID = 7843 AND i.Slot BETWEEN 0 AND 12 AND i.ItemID != 0

I am having problems with this query, I am not very good at joins as I would like.

t.OptLevel

always> = 0 from _Items

and string is always present

o.ReqLevel1

is always between 1 and 101 of _RefObjCommon

, and the string is always present

however b.nOptValue

from _BindingOptionWithItem

is either NULL, 1, or 2 from string, which doesn't always exist ... when b.nOptValue

= 1 or 2 is present in one of the 12 row results (i.Slot BETWEEN 0 AND 12) the script works fine: I get the sum, but if it b.nOptValue

returns NULL on all 12 row results, the sum is returned NULL for the entire query.

I know there is a simple solution, but I cannot find it.

+3

sql join sum

Scott timlock 11 Feb At 15:09

source to share

3 answers

fancyPants · Answer 1 · 2013-02-11T15:24:21+0000

Without understanding my question, there is this cool function COALESCE()

that returns the first of its parameters, which is not null.

SELECT COALESCE(SUM(t.OptLevel), 0) + COALESCE(....

Gordon linoff · Answer 2 · 2013-02-11T15:24:25+0000

The problem is that NULL + <anyvalue>

it is NULL.

Do you want to:

SELECT coalesce(SUM(t.OptLevel), 0) + coalesce(SUM(o.ReqLevel1), 0) + coalesce(SUM(b.nOptValue) , 0)
. . .

Tim lehner · Answer 3 · 2013-02-11T15:25:14+0000

I would probably start with something like this:

SELECT SUM(t.OptLevel) + SUM(o.ReqLevel1) + COALESCE(SUM(b.nOptValue), 0)
FROM _Inventory AS i 
    JOIN _Items AS t ON t.ID64 = i.ItemID
    JOIN _RefObjCommon AS o ON o.ID = t.RefItemID
    LEFT JOIN _BindingOptionWithItem AS b ON b.nItemDBID = i.ItemID 
WHERE i.CharID = 7843
    AND i.Slot BETWEEN 0 AND 12
    AND i.ItemID != 0

If the join will always have a match, then there is no need for an outer join (including a left join). If you can't get a match, you'll need a left join, then use COALESCE

that returns the first non-zero value it can find, which might be the constant you provide (0 in this case).

SUM () from a multipart JOIN

More articles: