Best solution

Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.

ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?

Terri|||If I understand correctly, can you do several UNIONs and get them in turn?

Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.

What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)

Let say I have a row that consist of the following:

TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null

key column contains the rowID

ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."

Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.

SELECT
 table1.key,
 table2key.name,
 table2key.value,
 table1.ele1,
 table2ele1.name,
 table2ele1.value,
 table1.ele2,
 table2ele2.name,
 table2ele2.value,
 table1.ele3,
 table2ele3.name,
 table2ele3.value,
 table1.ele4,
 table2ele4.name,
 table2ele4.value,
 table1.ele5,
 table2ele5.name,
 table2ele5.value
FROM
 table1
LEFT OUTER JOIN
 table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
 table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
 table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
 table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
 table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
 table2 AS table2ele5 ON table1.ele5 = table2ele5.key

Terri|||Thanks so much for all your help Terri!