Skip to Main Content

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!

inner join distinct rows

793686Aug 25 2010 — edited Aug 25 2010
I have two tables and i want to join them with "id", but id in second table is not unique

create table shav_temp1 (id int, name varchar(30));

create table shav_temp2 (id2 int, name varchar(30));

insert into shav_temp1 values(1,'sss');
insert into shav_temp1 values(2,'pss');
insert into shav_temp1 values(3,'sss');
insert into shav_temp1 values(4,'sss');
insert into shav_temp1 values(5,'css');


insert into shav_temp2 values(1,'sss');
insert into shav_temp2 values(1,'sss');
insert into shav_temp2 values(1,'sss');
insert into shav_temp2 values(2,'pss');
insert into shav_temp2 values(5,'css');


select distinct * from shav_temp1 s1,shav_temp2 s2 where s1.id=s2.id2


if I use above query I am getting non-duplicate records, but due to my application limitation i have to use this querry after "where" clause like below


select * from shav_temp1 s1,shav_temp2 s2 where exists(select distinct * from shav_temp1 s1,shav_temp2 s2 where s1.id=s2.id2)


but this query returns 25 records and i want the same results as subquery returns

Can someone please help me in that

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 22 2010
Added on Aug 25 2010
8 comments
15,343 views