How to get the RowID to put into an INSERT INTO statement

Question

I have two tables
virtual_domains
virtual_users.

virtual_domains has a list of valid domains for my emails server, example data:

    ID         Name
    1          example.org
    2          example.com
    3          example.net
    

virtual_users keeps the valid email addresses for each domain, and links them to the domain which they belong, example data:

    id         domain_id          password           email address
    1          1                 some               jack@example.org
    2          1                 thing              john@example.org
    3          3                 goes               annete@example.net
    4          2                 here               bilbo@example.com
    

So to insert a new user, I use this syntax (auto_increment is in use for the id column):

INSERT INTO databasename.virtual_users (
    id, 
    domain_id, 
    password, 
    email ) 
    VALUES (
    DEFAULT, 
    '3', 
    MD5('somepassword'), 
    'kurt@example.net');
    

and that will add kurt@example.net to the 5th row as an email user.

What I want to be able to do, is instead of putting in the '3' for the domain id, add some other syntax there to return the id from the virtual_domains table of the relevent domain, ie:

INSERT INTO `databasename`.`virtual_users` ( 
`id`, 
`domain_id`, 
`password`, 
`email` ) 
VALUES (
DEFAULT, 
*ADD_QUERY_HERE_TO_GET_DOMIAN_ID_VALUE_FROM_virtual_domains_TABLE*, 
MD5('somepassword'), 
'kurt@example.net' );

so that it can all be done in one command, rather than having to look up the domain_id in a seperate query.

Answer 1

You can use a subquery in an INSERT statement.

 INSERT INTO databasename.virtual_users (
     id, 
     domain_id, 
     password, 
     email
 ) 
     VALUES (
     DEFAULT, 
     ( SELECT ID FROM databasename.virtual_domains WHERE Name = 'example.net' ), 
     MD5('somepassword'), 
     'kurt@example.net'
 );

I have only tested this in SQL Server, but I think it should work in most databases.