009/9/15 Gert Cuykens <gert.cuyk...@gmail.com>:
> 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?
It's late, so I am not putting in the time to generate test data in
your schema, but you should probably ensure that your result column in
your result table is aliased properly:
SELECT t.pid,
t.txt,
t.price,
t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
FROM PRODUCTS t
LEFT JOIN (SELECT o.pid as pid, -- alias this column
SUM(o.qty) 'qty_sold'
FROM ORDERS o) qs ON qs.pid = t.pid
WHERE t.pid = ?;
it certainly resolves error messages about "qs.pid does not exist".
>
>
> 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)
> );
You should also be aware that single quotes are delimiters for
literals, not for identifiers. For identifiers use double quotes.
(SQLite is tolerant of the usage in the case above).
Regards,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
