SQL Question

lostlost Member Posts: 1 ■□□□□□□□□□
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

Comments

  • BeaverC32BeaverC32 Member Posts: 670 ■■■□□□□□□□
    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.
    MCSE 2003, MCSA 2003, LPIC-1, MCP, MCTS: Vista Config, MCTS: SQL Server 2005, CCNA, A+, Network+, Server+, Security+, Linux+, BSCS (Information Systems)
  • Se74Se74 Member Posts: 1 ■□□□□□□□□□
    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.
Sign In or Register to comment.