By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,302 Members | 3,621 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,302 IT Pros & Developers. It's quick & easy.

Removing Duplicates rows from Inner Join

P: n/a
i have two tables, tab1 having N1 col and tab2 N2 col. now N1 is subset
of N2.
I need the information from tab2 (having N2) of all rows having the
matching entry in N1 in tab1.
For this i am using Inner Join on cols N1 and n2. But result is giving
duplicate rows. Can anyone suggest how do u i remove those duplicate
rows? or may be a better way to do the above work... Thanks

Feb 9 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a


Sounds like you should be using EXISTS
rather than a join

SELECT * FROM tab2
WHERE EXISTS (SELECT * FROM tab1 WHERE tab1.N1=tab2.N2)

Feb 9 '06 #2

P: n/a
thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?

Feb 9 '06 #3

P: n/a
Did you try to use DISTINCT to remove the duplicates?
SELECT DISTINCT tab2.* FROM tab2 INNER JOIN tab1 ON tab2.N2 = tab1.N1

Feb 9 '06 #4

P: n/a

Can you post your DDL including indexes

Feb 9 '06 #5

P: n/a
asgars (as****@gmail.com) writes:
thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?


Which query? It's very difficult to suggest optimizations to a query
without seeing it, and without knowledge of the tables.

Please post:

o The query you are using now.
o CREATE TABLE and CREATE INDEX statements for the inolved tables.
o Some indication on number of rows in the table.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 9 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.