Null and 0

Piranha

I have a database like this:

[b]item[/b] |            | [b]itemproperty[/b] |            | [b]property[/b]
id   | ---------- | itemid       | ---------- | id         

name | 1        * | propertyid   | *        1 | name
                  | value        |

In other words it is a many-to-many realtionship that have a variable (value) in the table between. An item may have one or more properties, but is not required to have all of them. I want to order by, and output, the sum of the value of the itemproperty (it will be more advanced in the future), but can't really get it to work. If I have a item (chair) and have two properties (legs propertyid 1 value 4 and back support propertyid 2 value 1) I can do a query like this:

SELECT item.name, ip1.value as ipv1, ip2.value as ipv2, (ip1.value + ip2.value) as summ
FROM item
LEFT JOIN itemproperty as ip1 ON ip1.itemid = item.id AND ip1.propertyid = 1
LEFT JOIN itemproperty as ip2 ON ip2.itemid = item.id AND ip2.propertyid = 2
ORDER BY (ip1.value + ip2.value)

That works and I get the correct result. But if I add a stool (legs propertyid 1 value 4) but no back support I get a null value, and the whole math will end with null on that row. Is there another way to do this, or a way to get null into 0?

Edit: I added the table below to let you see how it is outputted

[b]name     ipv1     ipv2     summ[/b]
chair    4        1        5
stool    4        [i]NULL[/i]     [i]NULL[/i]      In the summ here I would like to get 4

leatherback

Hi,

I think you are looking for an outer join.

have a look at this page, which describes it quite well:

http://www.enterprisedb.com/documentation/tutorial-join.html

Piranha

Thank you for looking at it, however I already use two different left outer joins.

Edit: I should say that I don't want to order by just the sum, I want the different values to be able to be mulitplyed individually with values the user are inserting.

Lars_Berg

SELECT item.name, ip1.value as ipv1, ip2.value as ipv2, 
  coalesce(ip1.value,0) + coalesce(ip2.value,0) as summ
FROM item
LEFT JOIN itemproperty as ip1 ON ip1.itemid = item.id AND ip1.propertyid = 1
LEFT JOIN itemproperty as ip2 ON ip2.itemid = item.id AND ip2.propertyid = 2
ORDER BY summ

Piranha

Tusen tack Lars, jag hade inte hittat det utan hjälp.