SQL Server query for finding duplicate rows from identical tables

Question

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.

Not sure if understand the question. If you change places of "realTable" and "queryTable" - will it solve the problem? Can you provide some more details and maybe desired output? — Slava Murygin
– Slava Murygin, Commented Jan 26, 2019 at 2:56
No one can debug a query that is not posted. And your code contradicts your stated results. The queries select from searchTable only - both of them. It is impossible to get "all rows listed from the realTable". — SMor
– SMor, Commented Jan 26, 2019 at 14:31

iminiki · Accepted Answer · 2019-01-26 04:17:46Z

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.

Andrew James · Accepted Answer · 2019-01-28 22:57:47Z

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.

Collectives™ on Stack Overflow

SQL Server query for finding duplicate rows from identical tables

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related