Stix83 (2/7/2016)
@Hugo, my apologies I am just trying to get to see which method will work best as I am fairly new at SQL:1. what I need to be done functionally is this:
In the UI, when a user there is a field called code and it has a sub field called description. So in the front end when the user clicks code lm40.0, the subfield description needs to auto generate "asthma", I am basically matching a code with a description. each disease code has a description matching the code.
So if I select via the UI lm40.0 in the code field, the underneath in the subfield the description must auto populate to asthma
2. yes selecting the code in the UI but in the backend in sql there is a table called codes with columns code, description
3.The description value is in the codes table, it is associated with a code. Each code has its own description. I.e. Each disease code has a description.
apologies for my ignorance I just want to know the best route to take here
So as I expected, you are asking about a UI question. There's only little we can do here to help you with that.
Most UIs nowadays use some kind of event model, so when the user clicks Im40.0 it will fire an event handler. That event handler has to find out what code was clicked, then reach out to the database to get the corresponding description. The best way to do this is through a parametrized query. The query will look as follows:
SELECT Description
FROM YourTable
WHERE Code = @Code;
Assuming that your UI is object-oriented, you will feed this query string into a query object. You then add a parameter object to that query object, that declares the parameter @Code, assigns it the proper data type, and sets it value to Im40.0. Then you execute the query and process the results to find the description used, and populate that in the field on your UI's form.
(The above is the bare basic, most developers and most DBAs will prefer to have a stored procedure in the database and then the client calls that stored procedure instead of executing a freeform query. In either case the client code should also have error handling).
Depending on the programming language and tools used, an alternative would be to have the client application retrieve the full list of codes and descriptions once and cache it (store it locally - either on the application server or on the actual end client). The cached copy can then be used later without any need for round-trips to the database. This can be faster, especially when you are on a slow network or when the database server is under pressure. However, the list must not be too large to fit into memory on the client side. And you have to consider how often the data source changes and how bad it is that the application will continue to use the old data from its cache until the cache gets refreshed, either because the application restarts or because you build a cache refresh mechanism. If you do want to use caching, then the query you have to execute (probably at applicaiton startup or when the relevant form is first loaded) looks like this:
SELECT Code, Description
FROM YourTable;
As you see, there is no WHERE clause and no parameter. Make sure to process all rows and store them in a suitable object for local storage (I believe that in .Net a dictionary object is often used for this). Then use that object to find the description for a code in the event handler that starts when a code is clicked.
Long post and still quite vague. Again, for more specific help with writing client code, you probably should visit a forum for the language / front-end tool you are using.