[sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

   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, when i rename it to pid it subtracts
the wrong values when ORDERS contains one record and PRODUCTS more the
one?


CREATE TABLE PRODUCTS (
    pid     INTEGER PRIMARY KEY,
    txt     VARCHAR(64),
    price   BIGINT UNSIGNED,
    qty     BIGINT
);

CREATE TABLE ORDERS (
    oid     INTEGER,
    pid     BIGINT UNSIGNED,
    qty     BIGINT UNSIGNED,
    time   DATETIME,
    PRIMARY KEY(oid,pid)
);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users