INSERT INTO double select - how?

Question

I'm trying to insert two values from two select statements, something like:

INSERT INTO manytomany_relation_a_and_b_tables (a_id, b_id) VALUES 
(SELECT id FROM a_table WHERE someColumnFromA="SOMETHING"), 
(SELECT id FROM b_table WHERE someColumnFromB="SOMETHING");

Obviously it doesn't work, but how can I achieve effect which I want?

Answer 1

If each of the SELECT subqueries returns exactly one value, you can use INSERT INTO ... SELECT syntax:

INSERT INTO manytomany_relation_a_and_b_tables (a_id, b_id) 
SELECT (SELECT id FROM a_table WHERE someColumnFromA="SOMETHING"),
       (SELECT id FROM b_table WHERE someColumnFromB="SOMETHING")

Otherwise you need to specify a condition on which the two tables are joined together and perform JOIN in the SELECT query of the INSERT statement.

Answer 2

You also can use join,if those two tables have any thing in common..

INSERT INTO manytomany_relation_a_and_b_tables (a_id, b_id) 
VALUES 
select t1.id,t2.id
from a_table t1
join
b_table t2
on t1.something=t2.something
and t1.somecolumna='something' and t2.somecolumna='something'