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
