IssacJones
asked on
"lookup tables" in SQL
Hi
I need some advice on adding a certain table into an SQL/Windows client. The table looks something like this:
1 2 3 4 5 6
1<x<=2 4.0 2.0 5.0 3.4 6.7 4.7
2<x<=5 3.0 2.3 4.5 3.4 5.1 6.0
The table works in this way. Suppose the variable is y on the top of the table i.e. y = 1,2,3,4,5 or 6. If I have two values y = 3 and x = 1.3, then the table would give me the answer 5.0
My questio comes in two parts, firstly, what is the best way for me to add this type of table to an SQL database. Secondly, what would be the best way to interogate the database to get the answers for a given value of x and y?
Thanks in advance
John
I need some advice on adding a certain table into an SQL/Windows client. The table looks something like this:
1 2 3 4 5 6
1<x<=2 4.0 2.0 5.0 3.4 6.7 4.7
2<x<=5 3.0 2.3 4.5 3.4 5.1 6.0
The table works in this way. Suppose the variable is y on the top of the table i.e. y = 1,2,3,4,5 or 6. If I have two values y = 3 and x = 1.3, then the table would give me the answer 5.0
My questio comes in two parts, firstly, what is the best way for me to add this type of table to an SQL database. Secondly, what would be the best way to interogate the database to get the answers for a given value of x and y?
Thanks in advance
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi reb73
Yes, that makes sense to me. I'd have three columns in the table relating to x and y i.e. x_min, x_max and a column for the y values.
Could you give some example SQL code which I could use to interrogate the database?
John
Yes, that makes sense to me. I'd have three columns in the table relating to x and y i.e. x_min, x_max and a column for the y values.
Could you give some example SQL code which I could use to interrogate the database?
John
The 1st 2 solutions provide an easy method if there are a small amount of columns. However, if the # of X-axis values or Y-axis values can grow/vary greatly, I think my solution is a lot more robust.
ASKER
Hi derrekromm
Your suggestion looks promising and probably more generic. However, I'm only learning SQL so I don't follow everything. In particular, I'm not entirely sure how the intersect of the two tables works. I'm also not too familiar with the "inner join" part of the code.
It looks very interesting though! I'll have to get some reading done I think!
John
Your suggestion looks promising and probably more generic. However, I'm only learning SQL so I don't follow everything. In particular, I'm not entirely sure how the intersect of the two tables works. I'm also not too familiar with the "inner join" part of the code.
It looks very interesting though! I'll have to get some reading done I think!
John
the inner join does what it sounds like, it joins the 2 tables together on a column (or group of columns)
in this case, it connects the Intersect table with the X-axis table and the Y-axis table based on the ID values
your data would look like this
X-axis:
ID Min Max
1 1 2
2 2.01 5
...
Y-axis:
ID Val
1 1
2 2
3 3
4 4
5 5
Intersect:
ID XaxisID YaxisID Val
1 1 1 4
2 1 2 2
3 1 3 5
4 1 4 3.4
5 1 5 6.7
6 1 6 4.7
7 2 1 3
...
the "ID" fields are simply internal identifiers that are easier for the database system to query/join on
in this case, it connects the Intersect table with the X-axis table and the Y-axis table based on the ID values
your data would look like this
X-axis:
ID Min Max
1 1 2
2 2.01 5
...
Y-axis:
ID Val
1 1
2 2
3 3
4 4
5 5
Intersect:
ID XaxisID YaxisID Val
1 1 1 4
2 1 2 2
3 1 3 5
4 1 4 3.4
5 1 5 6.7
6 1 6 4.7
7 2 1 3
...
the "ID" fields are simply internal identifiers that are easier for the database system to query/join on
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys - excellent information from you both!
I hope you don't mind if I share the points equally between you both. You have both helped me enourmously.
Many thanks
John
I hope you don't mind if I share the points equally between you both. You have both helped me enourmously.
Many thanks
John
ASKER
sorry, "both" should have been "all". put it down to a long day!
(b) You could simply pass the x attribute as a parameter and use a double where clause looking for min_val >= parameter and max_val <= the passed x attribute parameter ..
Does this make sense?