Is it possible to query against these tables?

Question

I have table one which looks like this. And I want to get data like

1.2, "My name is " , 1.6, "Earl" ,12345, "Rock Hard Awesome"

I don't think it is possible with this schema but wanted to see if I am wrong. I tried a cross join but got ever possibility not just the actual values.

What is supposed to be accomplished is the values table is written a lot of times (for a ton of values) but the description table has the unique values that are static and by putting them in a separate table they do not get written over and over.

Values Table
Column names = DataID1|DataID1Value|DataID2|DataID2Value|DataID3|DataID3Value
Row values   = 1        1.2          2       1.6         3        12345

Description Table
Column names = DescriptionID1|Description
Row value   = 1               "My name is"
Row value   = 2               "Earl"
Row value   = 3               "Rock Hard Awesome"

Answer 1

How about this:

SELECT DISTINCT v.DataID1Value, d1.Description, 
                v.DataID2Value, d2.Description, 
                v.DataID3Value, d3.Description
  FROM Values v
  INNER JOIN Description d1 on d1.DescriptionID = v.DataID1
  INNER JOIN Description d2 on d2.DescriptionID = v.DataID2
  INNER JOIN Description d3 on d3.DescriptionID = v.DataID3

Answer 2

Personally I wouldn't store data that way. What you have is essentially a lookup table that is associated with many columns insted of just one. So you will have to join to it for every column that you want to see the description for. This is possible but will lead to horrible performance problmes as this table will become a locking nightmare.

If you have static values that don't change, look them up (use them for a pull down if you want on your forms) and store them inthe values table on insert. Then you can query only one table.

Then go and read about EAV tables and what a problem they are and why they are poor techinique in database design.

And this is how you query the current table. Note that you join to it mulitple times to get the information you need for each value. The d1, d2, d3 are the differnt aliases you need when you have multiple joins to the same table.

  SELECT DISTINCT v.DataID1Value, d1.Description,  
                v.DataID2Value, d2.Description,  
                v.DataID3Value, d3.Description 
  FROM Values v 
  INNER JOIN Description d1 on d1.DescriptionID = v.DataID1 
  INNER JOIN Description d2 on d2.DescriptionID = v.DataID2 
  INNER JOIN Description d3 on d3.DescriptionID = v.DataID3