Gert Cuykens <gert.cuyk...@gmail.com>
wrote:
> SELECT t.pid,
> t.txt,
> t.price,
> t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
> FROM PRODUCTS t
> LEFT JOIN (SELECT o.pid,
> SUM(o.qty) 'qty_sold'
> FROM ORDERS o) qs ON qs.pid = t.pid
> WHERE t.pid = ?
>
> i have trouble running this statement on sqlite3
> It tels me qs.pid does not exist
And indeed it doesn't. But there is a column named qs."o.pid". You may
want to assign an alias to this column, just as you did with qs.qty_sold
> when i rename it to pid it subtracts
> the wrong values
Because now it refers to t.pid, and the condition (t.pid = t.pid) is
always true.
Perhaps your query could be a bit clearer when written this way:
select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
from PRODUCTS t left join ORDERS o on t.pid = o.pid
where t.pid = ?;
or this way
select t.pid, t.txt, t.price, t.qty - IFNULL(
(select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
from PRODUCTS t where t.pid = ?;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
