1

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?

1 Answer 1

3

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)

2
  • 1
    Fun fact: While logically they are not the same, CROSS JOIN is an alias to JOIN in MySQL sqlfiddle.com/#!9/5a3d7/2
    – jynus
    Commented Jul 9, 2016 at 18:03
  • 2
    While the ON clause is optional with MySQL (unlike standard SQL), I think it is better to explicitly write CROSS JOIN when a Cartesian product is needed. A JOIN alone may look like the ON clause has been forgotten or removed by mistake. Commented Jul 11, 2016 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.