On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > 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.
qs."o.pid" = t.pid works thanks > 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 This does not show me the new products that are not ordered yet > 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 = ?; > I learned that this would be a performance issue doing it like that. http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
