CODEWITHSUNDEEP
sqlsql-server
Andrew James
Andrew James

Reputation: 41

SQL Server query for finding duplicate rows from identical tables

I have a question about finding identical rows from one table to another. I have a table for users to ask for information. So with that the query will be ran against another table. Both tables are identical except for the ID columns.

The ID columns are not involved in the query except for the 

SELECT TOP 1 * 
FROM searchTable 
ORDER BY searchid DESC

part.

My query looks like this

SELECT TOP 1 * 
FROM searchTable 
ORDER BY searchid DESC(SELECT A.column1, A.column2,..............
                       FROM dbo.searchTable A
                       WHERE EXISTS (SELECT * FROM realTable B 
                                     WHERE A.Column1 = B.Column1 
                                       AND A.Column2 = B.Column2,
                                       .......
                                       AND A.lastColumn = B.lastColumn))

What I get when running the query is the last entered query from the query table, which is correct, but I get all the rows listed from the realTable as if everything after WHERE EXISTS is pointless. What I need is the single row query from the queryTable to list all the rows that are identical to it from the realTable. Not all the rows the realTable has.

Upvotes: 1

Views: 63

Answers (2)

Andrew James
Andrew James

Reputation: 41

I was able to get it to work the way I needed by understanding the logic in the last suggestion.

It looks like this.

DECLARE @searchID int = (SELECT MAX(searchID) FROM searchTable)

SELECT Column1, Column2.............LastColumn FROM realTable B

WHERE EXISTS(SELECT * FROM searchTable A WHERE searchID = @searchID AND A.Column1=B.Column1 AND A.Column2=B.Column2................A.LastColumn=B.LastColumn)

Now the last search in the searchTable will give me all the rows in the realTable that match that search.

Upvotes: 0

iminiki
iminiki

Reputation: 2573

You can use inner join instead of exists.

select B.* from searchTable A
inner join realTable B
on A.Column1 = B.Column1
and A.Column2 = B.Column2
.
.
.

It will return all the records in your realTable which have identical columns with your searchTable.

Upvotes: 2

Related Questions