Combining a query for value and an insert MySQL I have 2 tables, ranks and accounts. When inserting into accounts I fetch the ID of the rank first, storing it in a variable on the script running the queries, then insert the new account into the table with the rank ID. How could I combine this into a single MySQL statement?

UPDATE:

// This is pseudo code.
var acct = GetAcct()
var a = MySQL.query("SELECT `id` FROM `ranks`
           WHERE `name`=" + acct.rankName).firstResult()
var b = MySQL.query("INSERT INTO `accounts` (`sid`, `did`,`rank`)
           VALUES (" + acct.sid + "," + acct.did + "," + a + ")
           ON DUPLICATE KEY UPDATE
               `did`=" + acct.did + ",`sid`=" + acct.sid + ",
               `rank`=" + a)
Use INSERT .. SELECT instead of INSERT .. VALUES:

INSERT INTO accounts (name       , login       , ..., rank        , ...)
SELECT                'some name', 'some login', ..., ranks.rankID, ...
FROM ranks
WHERE ranks.name = 'some rank' /* ORDER BY ranks.somefield LIMIT 1 */


The values You need to insert directly (name, login, etc.) are added into SELECT part literally, the data obtained from ranks table is selected by common way. If there exists more than 1 record in ranks appropriate to criteria You use - add ordering and limiting to select (and add into destination table) one record only.