On Wed, Sep 16, 2009 at 9:50 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Gert Cuykens <gert.cuyk...@gmail.com> > wrote: >> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik >> <itandet...@mvps.org> wrote: >>> Gert Cuykens <gert.cuyk...@gmail.com> >>> wrote: >>>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik >>>> <itandet...@mvps.org> wrote: >>> >>>>> 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 >>> >>> Are you sure? I don't see why it wouldn't. >> >> 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 >> >> shows 1 product because I only have 1 order with that product > > This query is different from the one I posted. Mine had "where t.pid = > ?;", yours doesn't. If you want a report for all products, add the > following clause: > > group by t.pid; > >> 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."o.pid" = t.pid >> >> shows all products > > Compare this statement with the one you mentioned in your original post. > Lacking mind-reading abilities, I was trying to help you with the > statement you actually asked about, not the one you were thinking about.
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 group by t.pid Works also thanks. So you do not believe the following has a performance penalty ? SELECT pid, txt, price, qty-coalesce((SELECT sum(qty) FROM orders WHERE orders.pid = products.pid),0) FROM products _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
