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. >> 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 You "learned"? Do you mean, you measured and discovered this to be the case? The answer in that thread you are basing this claim on is largely nonsense, in my humble opinion. For one thing, your query only returns one row, so running a subselect "for every row returned" means running it once. For another, how does the poster believe joins are calculated - black magic? Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
