SQL Server Lookup Tables

[shankel]

Hello,
I had an Access Data Project that I am converting to Web with .NET. The problem I'm having is I created the table structure with ADP using lookups. I have a Main table with all columns except MainID pointing to lookup tables.
When I run a query on the table in query analyzer it shows only the IDs from the lookup tables, and not the associated data. How do I recreate the lookups in SQL Server?
Thanks,
Shankel

 

[bertrandkis]

Here is how you can get the descriptions from the lookup tables instead of Ids.
Say your main tables has the following columns
MainId,Col1Lookup1,Col2Lookup2,Col3Lookup3,Col4Lookup4
the lookup columns are linked to the lookup tables Lookup1,Lookup2,Lookup3,Lookup4 respectively. Each of your lookup table has an ID column and a Description column say ItemId,ItemDescription. In query analyser use a join query to link up your tables like this:
Select MainId,Lookup1.ItemDescription,Lookup2.ItemDescription,Lookup3.ItemDescription,Lookup4.ItemDescription From MainTable Inner Join Lookup1 on Lookup1.ItemId=MainTable.Col1Lookup1
Inner Join Lookup2 on Lookup2.ItemId=MainTable.Col2Lookup2
Inner Join Lookup3 on Lookup3.ItemId=MainTable.Col3Lookup3
Inner Join Lookup4 on Lookup4.ItemId=MainTable.Col4Lookup4
If none of your lookup columns has a null value you should get all your records, if they do have nulls replace the keyword Inner by Left.
Since you are new to the wonderful world of sql server,open the help file and read up on joins.
I hope this helps.
Bertrandkis @ Blackbeltsequelist

 

[shankel]

Great. That gives me exactly what I need.
I was kind of moving in that direction, but I didn't know
if that was the way to do it. You have been a tremendous
help. Now, how would I turn that into an update query.
My end plan is to have an updateable grid on a web form.
I can update a single table, but I can't figure out how to update my Main table, because it has all of the lookup columns.