tricky statement...

This is my problem.

I have 10 different codes in a table.

Another table has descriptions of the codes.

I am trying to come up with a select statement than will change the codes to their descriptions per row. Each row has 10 different codes.

Not sure how I do this!

Quick question. Why not just put them both in one table.. Would make it a lot simpler for you.

Say table one (code) is like
codekey,CodeText, descriptionId
and the table two (description) is
descriptionId, DescriptionText
then your select is:

Code:

---

select CodeText,DescriptionText
from
Table1, Table2
where
table1.descriptionId=table2.descriptionId
and table1.descriptionId is not null

---

The above will list all related itmes, skipping those for which
you do not have a value in both

If you want, you can use a join query:

Code:

---

select CodeText,DescriptionText
from
Table1
inner Join
Table2
on
Table2.descriptionId=Table1.descriptionId

---

The problem is that all of these codes are different columns in one row. So I need to match these codes with a description from one other table.

I cant change the table layout. I didnt design it and I am stuck with it and I really want to kill the person who designed it with these arrays of values. Its killing me.

Do you really want/need to replace them, or can you just display the descriptions (multiple per row).

In general keeping the descriptions in a seperate table is a good idea (data normalization). Kind of a moot point since you cant change the schema in any case...

Code:

---

SELECT Table2.ID,
Table1_1.Descript AS D1, Table1_2.Descript AS D2, Table1_3.Descript AS D3, Table1_4.Descript AS D4

FROM
(((Table2 INNER JOIN Table1 AS Table1_1 ON Table2.Code1 = Table1_1.Code)
INNER JOIN Table1 AS Table1_2 ON Table2.Code2 = Table1_2.Code)
IINNER JOIN Table1 AS Table1_3 ON Table2.Code3 = Table1_3.Code)
INNER JOIN Table1 AS Table1_4 ON Table2.Code4 = Table1_4.Code;

---