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