SQL Question

lost

Hi,

After a break of some 3 yrs I am trying to write a simple query and realized that I am kind of lost.

I have 2 tables

table1
group_id description
1 abc
2 def
3 ghi
4 xyz

table2

name option1_group_id option2_group_id
aaaa 1
bbbb 2
dddd 3
sean 2 1
xxxx 2 3
gggg 2 4
jame 3 1
baaa 3 2
wend 3 4


I need to display the information from the above 2 tables in the following format wherein all the group ids in table2 is replaced with there actual description. Any help here would be appreciated.

name option1_group_id option2_group_id
aaaa abc
bbbb def
dddd ghi
sean def abc
xxxx def ghi
gggg def xyz
jame ghi abc
baaa ghi def
wend ghi xyz

Many Thanks

BeaverC32

Your example is a little confusing based on the column names you gave, but this is what I THINK you are looking for:

SELECT Table2.name, Table1.description
FROM Table1, Table2
RIGHT OUTER JOIN Table1
ON Table2.option1_group_id = Table1.group_id

UNION

SELECT Table1.description
FROM Table1, Table2
RIGHT OUTER JOIN Table1
ON Table2.option2_group_id = Table1.group_id


There may be easier ways to do this, but I believe this will do the trick.

Se74

Using left joins with two columns would solve your problems. First join gives the value for op1 and second join gives the value for op2.

select t2.nme,t1.descrip,t3.descrip

from table1 t1 left join table2 t2 on t1.group_id = t2.op1

left join table1 t3 on t3.group_id = t2.op2

where t2.nme is not null

Hope it will solve your problem.