Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,005 Comments

Discussions

Need query

user508399
user508399 Member Posts: 37
edited February 2007 in SQL & PL/SQL
table1:
eno ename
1 madhan
2 kumar
3 wills
4 kou

table2:
eno1 eno2
1 2
3 4

Output:
eno1 ename eno2 ename
1 madhan 2 kumar
3 wills 4 kou

I need a query for getting this output. I tried with using one more instance of table1. It works fine. But i want to know is there any other way.

Comments

  • ushitaki
    ushitaki Member Posts: 1,128
    edited February 2007
    What means 'instance of table1' ?

    select
    t2.eno1
    ,t11.ename
    ,t2.eno2
    ,t12.ename
    from table1 t11,table1 t12,table2 t2
    where t11.eno = t2.eno1
    and t12.eno = t2.eno2
    ;

    Have you already tried as above?
  • user508399
    user508399 Member Posts: 37
    Thanks for your reply. I have already tried in your way. I got the result. But i need to know if there is any other way.
  • ushitaki
    ushitaki Member Posts: 1,128
    edited February 2007
    I see, then...

    select
    t2.eno1
    ,max(decode(t1.eno,t2.eno1,t1.ename,null))
    ,t2.eno2
    ,max(decode(t1.eno,t2.eno2,t1.ename,null))
    ,t12.ename
    from table1 t1, table2 t2
    where t1.eno = t2.eno1
    or t1.eno = t2.eno2
    group by t2.eno1,t2.eno2
    ;

    select
    t2.eno1
    ,(select name from table1 t1 where t1.eno = t2.eno1)
    ,t2.eno2
    ,(select name from table1 t1 where t1.eno = t2.eno2)
    ,t12.ename
    from table2 t2
    ;

    But, you should compare the performances between these and your query.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    with table1 as(
    select 1 as eno,'madhan' as ename from dual
    union select 2,'kumar' from dual
    union select 3,'wills' from dual
    union select 4,'kou' from dual),
    table2 as(
    select 1 as eno1,2 as eno2 from dual
    union select 3,4 from dual)
    select
    b.eno1,max(decode(a.eno,b.eno1,a.ename)) as ename1,
    b.eno2,max(decode(a.eno,b.eno2,a.ename)) as ename2
    from table1 a,table2 b
    where a.eno in (b.eno1,b.eno2)
    group by b.eno1,b.eno2;
  • Billy Verreynne
    Billy Verreynne Member Posts: 28,268 Red Diamond
    There are other ways this can be done implementation and syntax wise - however, irrespective of the way, table 1 needs to be queried twice for every row from table 2.

    Example:
    SELECT
      t2.id1,
      (select t1.name from table1 t1 where t1.id = t2.id1) as "NAME1",
      t2.id2,
      (select t1.name from table1 t1 where t1.id = t2.id2) as "NAME2"
    FROM table2 t2
    The method that you have already tried, joining twice to table1, is the standard practice and makes a lot more sense than the above "clumsy" syntax.
This discussion has been closed.