[sqlite] VIEW produces strange result

[JC]

VIEW produces strange result set, which is different from 
theselect-statement that created the VIEW does. I'll use a simple 
databaseto show the problem.

My current Sqlite version is : 2.8.11 running on WinXP

definition: a simple database to manage purchase and sale of a book
---------------
CREATE TABLE buy(book,num);
CREATE TABLE sell(book,num);
INSERT INTO buy values(1,10);
INSERT INTO sell values(1,5);
INSERT INTO sell values(1,5);
so now I have two tables like these:

table buy:
sqlite> select * from buy;
book        num
----------  ----------
1           10
table sell:
sqlite> select * from sell;
book        num
----------  ----------
1           5
1           5
now create a view to see how many book 1 are sold:

CREATE VIEW v_sell AS
  SELECT book,sum(num) AS num
  FROM sell
  GROUP BY book;
view v_sell:
sqlite> select * from v_sell;
book        num
----------  ----------
1           10
then create a view to see how many book 1 are in stock:

CREATE VIEW v_stock AS
  SELECT buy.book AS book,buy.num - v_sell.num AS stock
  FROM buy,v_sell
  WHERE buy.book=v_sell.book;
but this produces a strange table:
sqlite> select * from v_stock;
book        stock
----------  ----------
1           0
1           0
there should be only one row in VIEW v_stock, but it gives out two. when 
run the query
  SELECT buy.book AS book,buy.num - v_sell.num AS stock
  FROM buy,v_sell
  WHERE buy.book=v_sell.book;
in CREATE VIEW v_stock alone, it works just fine, and produces table as 
this:
sqlite>         SELECT buy.book AS book,buy.num - v_sell.num AS stock
 ...>         FROM buy,v_sell
 ...>         WHERE buy.book=v_sell.book;
book        stock
----------  ----------
1           0

I wonder why VIEW v_stock produces a table which is not exactly the same 
as the select-statement created it does. Need some help ...   :(



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]