SQL Insert into a table using queries from two other tables

Question

I have a table of Tags and a table of Products:

Tags

ID  | Name | 
----|-------
1   | "Wunderbar!"
2   | "Brand-new"
3   | "Electrified!"

Products

 ID  | Name 
-----|---
1    | "Go-karts"
2    | "Rocketsleds"
3    | "Turbopants"
4    | "Sporks"

I have the list of tags and products in a CSV file and I need to generate the SQL that will insert the rows into the ProductTags table. So, eg, given the instructions "Electrified" -> "Sporks", "Electrified" -> "Rocketsleds" and "Wunderbar!" -> "Turbopants" I need to generate SQL that would make the table look like:

ProductTags

ID | TagId | ProdID
---|-------|-------
1  | 3     | 4
2  | 3     | 2
3  | 1     | 3

I've tried to construct the SQL using a pair of subqueries, as well as a join style solution like in this SO question, but after a few hours of this, still haven't gotten the syntax correct.

The specific SQL version is for MySQL/InnoDB. Thanks for any help/pointers.

Answer 1

Use IN will do

INSERT INTO product_tags ( tag_id, product_id )
SELECT tag_id, product_id
FROM tags
CROSS JOIN products
WHERE (tag_name, product_name) IN (
 ('Electrified', 'Sporks'),
 ('Electrified', 'Rocketsleds'),
 ('Wunderbar!', 'Turbopants')
);

demo