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);
Tried:
set @productid = select product.pid
from product
where product.articlenum = articlenum;
This returns result like:
PID
1
2
3
Required Result:
PID
1
If I am unclear, please ask.