Insert a row into a table with 2 foreign keys

[vavc1980]

Ok, here's my problem, I'll try to explain it the best I can, it may be something really simple.
I'm working in a .NET web page to add new users into the table in SQL.
I have the main table that will be updated with the new information, this table has 2 foreign keys (ids), each one of them to different tables.
Problem comes when a new user is been added, the web page displays the descriptions for these 2 fields, but in the main table I have the id's.

How can I insert the data in one single query instead of creating first 2 queries to get the id's based on the descriptions saving them into variables, and then finally insert all values to the main table.

Example:
I want to insert: 'NameofGuy', 'Administrator' , 'CompanyX'
into TableA.
TableA fields: username,role,c ompany
but role and company are ids (int), it should look like this:
'NameofGuy', 1, 2

TableB (roles): id, roleDescription
____________1 , 'Administrator'

TableC (company): id, companyName
_______________ __1, 'CompanyW'
_______________ __2, 'CompanyX'

Hope is clear.

Any help is appreciated.

[ck9663]

Option 1:

Create an updateable view. During updating of the view, update the underlying tables, instead.

Option 2:

On your app, you can build your query in such a way that you can update the tables with the necessary values.

Good luck!

-- CK

[iburyak]

Try this:

insert into tableA values ('NameofGuy', (Select Top 1 id from tableB where roleDescription = 'Administrator'), (Select Top 1 id from tableC where companyName = 'CompanyX'))

I use Top 1 to make sure that only one record will be returned you can use max or min as well.

Good Luck.