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
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
>>> 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?
>
The where t.pid=? should have been removed from my original question,
so it show the complete list of products.
I did not measured it, it sounded logic.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
