I have a product table and a product varieties table.
**Product Table** pid articlenum desc price **Product Varities Table** pvid, sizeid, materialid, pid fk quantity barcode
I am creating an Interface where a user can ADD a new product, I have a stored proc, with two statements. First will execute insert into product table and the other into product varieties table.
Issue I have is, how do I insert a product id into product varieties table using the article id – each article can have multiple varieties such as s, m, l, xl, xs?
Using the code below, I cannot do that because it returns more than one record on select for each article.
INSERT INTO product (articlenum,pdesc,price) VALUES (articlenum, pdesc,price); set @productid = select DISTINCT product.product_id from product where product.articlenum = articlenum; INSERT INTO product_varities (sizeid,materialid,pid,quantity,barcodevalue) VALUES (sizeid, materialid, @productid, quantity, barcode);
set @productid = select product.pid from product where product.articlenum = articlenum;
This returns result like:
PID 1 2 3
If I am unclear, please ask.