January 6, 2005 at 5:39 pm
from a newbie (and thanks for your patience):
When I need the descriptive interpretation of a value in a column, is the most efficient thing to do a lookup in the Select statement for loading a form? In MS Access, I do this with a DLookUp function. The value is in another lookup table. How would I write this in SQL?
SMK
January 7, 2005 at 11:00 am
This is what I think you are asking: You have a table with a column storing a code. There is another table that lists all of the possible codes and the descriptions. One thing you can do is to join the two tables, then the description will be part of the records that you bring back.
If your form is also for updating data, you may want to use a dropdown box instead. The value of the dropdown box is the code, but the user sees the description.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 7, 2005 at 11:57 am
Thanks. I have around 20 such columns. So is the most efficient thing to do to make 20 joins? This is actually a report.
SMK23
January 7, 2005 at 12:08 pm
Yes, it is better to do the 20 joins in one query than to go back to the server 19 times for descriptions in each row. Each of your dlookup calls has to query the database for each column X each row.
It would probably be better to either create a view or, if your reporting software can use it, a stored procedure.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 7, 2005 at 3:34 pm
That's the info I needed. Thanks.
SMK
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply