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?