Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select Query

468585Jan 30 2006 — edited Nov 4 2010
Hi,
I have 2 different tables,both has Emplid as key. Now i need to fetch values from Value1 and Value2 from table 2,by matching emplid in table 1.
I shuld get my out put something like below. Both Emplid and Emailid Shuld be fetched once from table.

Emplid Email ID Value1 Value2
00001 aa@test.com Ab 123
CD 345
DF 455

00002 bb@test.com AA 089
BB 999

Is it possible with select query.

Thanks
Durai

Comments

Warren Tolentino
in your sample output is the data below
CD 345
DF 455
are from column value1 and value2 of table 2? thanks.
468585
Yes those are value1 and value2. Can you post a syntax for the same?
Warren Tolentino
you can use the BREAK command in SQL*Plus:
SQL> select * from table1;

EMPLID     EMAILID
---------- --------------------
00001      aa@test.com
00002      bb@test.com

SQL> select * from table2;

EMPLID     VALUE1     VALUE2
---------- ---------- ----------
00001      Ab         123
00001      CD         345
00001      DF         455
00002      AA         089
00002      BB         999

SQL> Break on emplid on emailid
SQL> Select t1.emplid, t1.emailid,
  2         t2.value1, t2.value2
  3    from table1 t1,
  4         table2 t2
  5   where t1.emplid = t2.emplid;

EMPLID     EMAILID              VALUE1     VALUE2
---------- -------------------- ---------- ----------
00001      aa@test.com          Ab         123
                                CD         345
                                DF         455
00002      bb@test.com          AA         089
                                BB         999

SQL>
hope this helps.
468585
Hi Warren,
Thanks for your reply. I need the ouput in the format you had sent. But is it possible to write a query without break statement.
Warren Tolentino
it is possible if you are using oracle reports.
Jens Petersen

Using Warrens sample

Select decode(rn, 1, emplid, null) emplid,
       decode(rn, 1, emailid, null) emailid,
       value1, 
       value2
from (select t1.emplid, 
             t1.emailid,
             t2.value1, 
             t2.value2,
             ROW_NUMBER() OVER (PARTITION BY t1.emplid, t1.emailid ORDER BY t2.value1, t2.value2) rn
        from table1 t1,
             table2 t2
       where t1.emplid = t2.emplid
      ) t
order by emplid, emailid, rn;
468585
Thanks Petersen...It worked...
Aketi Jyuuzou 
create table table1(
EmpID char(5),
Email char(11));

insert into table1 values('00001','aa@test.com');
insert into table1 values('00002','bb@test.com');

create table table2(
EmpID  char(5),
Value1 char(2),
Value2 char(3));

insert into table2 values('00001','AB','123');
insert into table2 values('00001','CD','345');
insert into table2 values('00001','DF','455');
insert into table2 values('00002','AA','089');
insert into table2 values('00002','BB','999');
commit;

select
case Row_Number() over(partition by a.EmpID order by b.Value1,b.Value2)
when 1 then a.EmpID end as EmpID,
case Row_Number() over(partition by a.EmpID order by b.Value1,b.Value2)
when 1 then a.Email end as Email,
b.Value1,b.Value2
 from table1 a,table2 b
where a.EmpID = b.EmpID
order by a.EmpID,b.Value1,b.Value2;
OracleSQLPuzzle
http://www.geocities.jp/oraclesqlpuzzle
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 6 2006
Added on Jan 30 2006
8 comments
2,183 views