I want to insert the transactions data (product_id, buy, sell, desc) into
the sale_products table. But I want a mechanism whereby if I enter the
product_id, then the buy, sell, desc columns are auto entered (copied)
from their corresponding row in the products table.
Given:
CREATE TABLE products ( -- I believe this is a view of UNIONs, but this
should be a close approximation
product_id INTEGER PRIMARY KEY,
buy REAL,
sell REAL,
desc TEXT
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
product_id INTEGER, -- FK to products table
customer_id INTEGER -- FK to customes table
);
CREATE TABLE sale_products (
sale_id INTEGER, -- FK to sales table
product_id INTEGER, -- FK to products table
buy REAL,
sell REAL,
desc TEXT
);
Yes, getting your database closer to 3NF would be better. But as a quick
fix, could you do this on insert just after entering the sale record into
the sales table?
INSERT INTO sale_products (sale_id, product_id, buy, sell, desc)
SELECT s.sale_id, s.product_id, p.buy, p.sell, p.desc
FROM sales s INNER JOIN products p
ON s.product_id = p.product_id
WHERE s.sale_id = @sale_id; -- sqlite3_last_insert_rowid()
Or, if you know sale_id and product_id, save the JOIN:
INSERT INTO sale_products (sale_id, product_id, buy, sell, desc)
SELECT @sale_id, @product_id, buy, sell, desc
FROM products
WHERE product_id = @product_id;
- Trey
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
