How to insert values to table where one column needs a single value from select statement and another column needs multiple values from another select

Question

Table1 product: productname , productID

Table2 alias: product_alias, components, id

Table3 mapping: productId, alias_component_id

INSERT INTO `mapping`(`productId`,`alias_component_id`) VALUES ((SELECT productID FROM product WHERE NAME='abc'), (SELECT id FROM alias WHERE product_alias='xyz'));

The first select would return single value while second select would return multiple values. Basically, for single productId, I want to loop through and insert multiple alias ids. I am getting Subquery returns more than 1 row error

Tried INSERT INTO `mapping`(`alias_component_id`) (SELECT id FROM alias WHERE product_alias='xyz')) but this would leave productID column null.

Answer 1

I think you want the INSERT ... SELECT syntax:

INSERT INTO `mapping`(`productId`,`alias_component_id`)  
SELECT p.productID, a.id
FROM product p 
CROSS JOIN alias a 
WHERE p.name = 'abc' AND a.product_alias = 'xyz'

Answer 2

You don't need to insert with VALUES part, but with SELECT. In this select you join your tables on the logic you need.