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. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
