I have two tables xx1 & xx2.I need to join based on id,some times table xx1 id may have same ,in this case i have to join two tables xx1 & xx2 if the id in xx1 is only once.Below are my table creation and insert statements.
SQL> create table xx1 (id number,real varchar2(10));
Table created.
SQL> insert into xx1 values (1,'abc');
1 row created.
SQL> insert into xx1 values (2,'xyz');
1 row created.
SQL> insert into xx1 values (2,'fghu');
1 row created.
SQL> insert into xx1 values (3,'ffef');
1 row created.
SQL> create table xx2 (id number,realdata varchar2(10));
Table created.
SQL> insert into xx2 values (1,'zfsf');
1 row created.
SQL> insert into xx2 values (2,'Faf');
1 row created.
SQL> insert into xx2 values (3,'afaff');
1 row created.
SQL> select * from xx1;
ID REAL
---------- ----------
1 abc
2 xyz
2 fghu
3 ffef
SQL> select * from xx2;
ID REALDATA
---------- ----------
1 zfsf
2 Faf
3 afaff
SQL> desc xx1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
REAL VARCHAR2(10)
SQL> desc xx2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
REALDATA VARCHAR2(10)
SQL> select * from xx1,xx2 where xx1.id=xx2.id;
ID REAL ID REALDATA
---------- ---------- ---------- ----------
1 abc 1 zfsf
2 xyz 2 Faf
2 fghu 2 Faf
3 ffef 3 afaff
I want the output as below (Eliminating the id 2 as it repeated in the table xx1)
ID REAL ID REALDATA
---------- ---------- ---------- ----------
1 abc 1 zfsf
3 ffef 3 afaff
Thanks,