query problem

nightowl

this query is giving me problems :

$sql = "select u.username, u.cash, (u.cash + sum (s.value * p.number)) as total_value
from (users u left join (portfolio p left join shares s on s.code = p.share) on u.user_id = p.user_id)
group by u.username, u.cash
order by total_value desc";

For all those people who don't have any records in table "portfolio", I get an " " as value of "total_value". This is not the same as the cash (which is returned correctly). However, since there are no records in portfolio, in that case cash and total_value should be the same. anybody know why ?

nightowl

You might want to know this too ...

As you can see, both fields which are multiplied are figures (integer and real). That shouldn't be any problem.

[deleted]

You are using LEFT JOIN, which means that all records where portfolio does not have a matching value will give NULL for p.number. And you cannot do any calculations involving a NULL, it will always come out NULL, or " " as you see it in your app.

Try this on the commandline:

SELECT NULL + 4;

output: NULL

nightowl

Do you have a solution for it too ?

Situation (if you didn't know yet) is as follows :

table users :
user_id and cash

table portfolio
user_id, sharecode, number_of_shares

table shares
sharecode, value

I'd like to count the total value of ones portfolio... (cash + total amount of sharevalue). is it possible with one query or not ?

[deleted]

You could try using a CASE to substite 0 (zero) for the NULL:

SELECT
CASE
WHEN field IS NULL
THEN 0
ELSE field
END AS field
FROM table;

or in your case, something like

SELECT
SUM (CASE
WHEN field IS NULL
THEN 0
ELSE field*otherfield
END) AS field
FROM table;