468,242 Members | 1,585 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Insert a row into a table with 2 foreign keys

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.

I want to insert: 'NameofGuy', 'Administrator', 'CompanyX'
into TableA.
TableA fields: username,role,company
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.
Sep 23 '08 #1
2 1505
2,878 Expert 2GB
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
Sep 23 '08 #2
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by EricRobineau | last post: by
2 posts views Thread by Victor M. | last post: by
1 post views Thread by nomorems | last post: by
1 post views Thread by Philippe Lang | last post: by
8 posts views Thread by nano2k | last post: by
1 post views Thread by Wes Groleau | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.