So I had the following (genericized), which works just fine for me (barring errors in making the example generic) :
DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 DATETIME) BEGIN INSERT INTO `db`.`table1` ( `col1`, `col2`, `col3`, `col4`, `col5` ) SELECT val1, date1, date2, 1, `colA` FROM `table2` WHERE `colB` = val1; END// DELIMITER ;
But now I want to INSERT into a table (table1) an additional value (col6) SELECTed from a third different table (table3) upon which - as best as I can figure - there is nothing sensible to JOIN in one SELECT statement.
Something like :
DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 DATETIME, IN string1 VARCHAR(255)) BEGIN INSERT INTO `db`.`table1` ( `col1`, `col2`, `col3`, `col4`, `col5`, `col6` ) VALUES ( val1, date1, date2, 1, (SELECT `colA` FROM `table2` WHERE `colB` = val1), (SELECT `colZ` FROM `table3` WHERE `colY` = string1) ); END// DELIMITER ;
colA and colB from table2 are guaranteed UNIQUE within the table. Similarly, colZ and colY from table3 are guaranteed UNIQUE within the table.
val1/colB is PK in table2; colA is an FK to a fourth table (not important). colZ is PK in table3; string1/colY is not referenced anywhere else in the database.
In summary : col6 in table1 has an FK to table3's colZ. Hence, I want to INSERT into table1's col6 the INT of colZ based on a known unique, elsewhere unreferenced string in colY, while still being able to SELECT FROM table2 the FK value - colA - of a fourth unrelated table based on a given/known PK value colB and INSERT that into table1's col5.
How can I create a stored procedure that's basically the second example above ... in a form that MySQL will accept ?
(In case it's not super-clear from the example, col1 to col6 belong to table1; colA and colB belong to table2; colZ and colY belong to table3.)