inner join distinct rows
793686Aug 25 2010 — edited Aug 25 2010I 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