I use MySQL as the database backend behind most of my web projects, and I recently came across a situation where I needed to insert a foreign key into a table when I didn't know what the foreign key was. I knew I could do two queries, the first to look up the foreign key and the second to insert the data, but I wanted a more elegant solution. After poking around in the fine manual and not finding what I was looking for, I stumbled over a forum posting that indicated that I could do a nested select within my insert statement.

Here are my tables:
PropertyTypes
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| PropertyTypeID | int(11) | | PRI | NULL | auto_increment |
| PropertyTypeCode | char(3) | | | | |
| PropertyTypeDesc | varchar(64) | | | | |
+------------------+-------------+------+-----+---------+----------------+
Resorts
+-----------------+---------------+----------------+
| Field | Type | Extra |
+-----------------+---------------+----------------+
| ID | int(11) | auto_increment |
| Name | varchar(200) | |
| Description | text | |
| StreetAddress | varchar(255) | |
| StreetAddress2 | varchar(255) | |
| City | varchar(60) | |
| State | char(2) | |
| PostalCode | varchar(12) | |
| Country | varchar(100) | |
| Region | varchar(60) | |
| PropertyTypeID | int(11) | |
+-----------------+---------------+----------------+
And now I want to insert a new property record, but it's property type is "MOD" and I need to get the PropertyTypeID to insert as a foreign key into the Resorts table. Here's the query:

[sql]

INSERT INTO Resorts VALUES('','Some Hotel','Default description', 'Here','There','Manchester','NH','03102','USA','53',(SELECT PropertyTypeID FROM PropertyTypes WHERE PropertyTypeCode = 'MOD'));

[/sql]

The single value returned by the nested select is what gets inserted as the field value.

[tags]mysql,database, query, SQL,select,insert,nested,subselect,foreign,key,code,beyrent[/tags]