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:

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

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]

Related items