Inserting database row with values from another table

Basically, I have two tables: images and servers. When I want to insert a row into the images table, I need to specify a s_id as one of the fields. Problem is, I only have name, which is another field in the servers table. I need to find what s_id belongs to name, and then use that in my INSERT INTO query on the images table.

Maybe this image will help: http://i.imgur.com/rYXbW.png

I only know the name field from the servers table, and I need to use it to get the s_id field from the servers table. When I have that, I can use it in my INSERT INTO query, as it's a foreign key.

I found this: http://w开发者_JAVA百科ww.1keydata.com/sql/sqlinsert.html

But it just confused me even more.

One solution would be to run two queries. One to get the s_id, and one to run the insert query. But I'd like to limit the amount of queries I run if there's a reasonable alternative.

Thanks!

You can use the INSERT ... SELECT form, something like this (with real column names and values of course):

INSERT INTO images (s_id, u_id, name, filename, uploaded)
SELECT s_id, ...
FROM servers
WHERE name = 'the name'

I don't know where you're getting the u_id, name, filename, or uploaded column values for images but you can include them as literal values in the SELECT:

INSERT INTO images (s_id, u_id, name, filename, uploaded)
SELECT s_id, 11, 'pancakes', 'pancakes.jpg', '2011-05-28 11:23:42'
FROM servers
WHERE name = 'the name'

This sort of thing will insert multiple values if servers.name is not unique.

You should be able to do something like this, but you'll need to fill in the items in <> with the values you want to insert.

INSERT INTO images (s_id, u_id, name, filename, uploaded)
   (SELECT s_id, <u_id>, <name>, <filename>, <uploaded>
   FROM imgstore.servers
   WHERE name = @server_name)

This is the syntax for SQL Server, but I think it will work with MySQL as well.

Here's an article on INSERT ... SELECT Syntax

Please see my comment above regarding a potential data integrity issue. I am assuming that the name field in your server table has a unique constraint placed on it.

There are a couple of ways that you can approach this INSERT, and I'm sure that some are better than others. I make no claim that my way is the best way, but it should work. I don't know how you're writing this query, so I'm going to use @FieldValue to represent the variable input. My approach is to use a subquery in your insert statement to get the data that you require.

INSERT INTO images (field1, field2... s_id) VALUES ('@field1val', '@field2val'... (SELECT s_id FROM servers WHERE name='@nameval'));