Mysql – Using a subquery in place of a SELECT fieldname …or how to achieve the same result

insertMySQLsubquery

My goal is to get a collection of ID's from Table 1 based on a query, a collection of ID's from Table 2 based on a query, and insert a new record into Table 3 for each permutation.

Basically, this:

INSERT INTO `TABLE3` (Field1,Field2,Field3)
SELECT null,`UID`, (SELECT `UID` FROM `TABLE2` WHERE `IsDefault` = 1)
FROM `TABLE1`
WHERE `Group` LIKE 'V';

So if the subquery:

(SELECT `UID` FROM `TABLE2` WHERE `IsDefault` = 1)

returns a list of IDs:

1, 2, 3

And the main select query:

SELECT null,`UID`, $i
FROM `TABLE1`
WHERE `Group` LIKE 'V';

returns another list of IDs:

200, 300, 400

Then TABLE3 after the query runs should be:

1 - 200
1 - 300
1 - 400
2 - 200
2 - 300
2 - 400
3 - 200
3 - 300
3 - 400

When I tried the above query, I get:

#2014 - Commands out of sync; you can't run this command now

So I'm not sure what the proper syntax is to do an INSERT…SELECT where one of the "hard values" should be treated as a "foreach" variable from the subquery results?

Best Answer

What you want to do is called a Cartesian product and uses CROSS JOIN:

SELECT NULL, t1.UID, t2.UID
FROM table1 t1
CROSS JOIN table2 t2
WHERE T1.`Groups` LIKE 'V'
    AND t2. `IsDefault` = 1;

This mixes each rows from table1 with each rows from table2. (see sqlfiddle)

Related Question