How to Find Exact Duplicate Records Across Multiple Columns in SQL?

Finding Duplicate Values in Multiple Columns

In your SQL code, you seek to identify duplicate records based on specific criteria. To achieve this, you can utilize a combination of grouping and conditional filtering.

Your provided example aims to find duplicate records that match both the name and city columns. However, your original query has a slight issue: it also includes records where only the name or city matches, rather than requiring an exact match on both.

To resolve this issue, you can modify the query as follows:

SELECT s.id, t.*
FROM [stuff] s
JOIN (
    SELECT name, city, COUNT(*) AS qty
    FROM [stuff]
    GROUP BY name, city
    HAVING COUNT(*) > 1
) t ON s.name = t.name AND s.city = t.city

This query joins the [stuff] table with a subquery that calculates the count of duplicate records for each unique pair of name and city values. By setting the HAVING clause to only include groups with a count greater than 1, you can limit the results to rows that represent actual duplicates.

The output of this query should match your desired result:

id	name	city
904834	jim	London
904835	jim	London
90145	Fred	Paris
90132	Fred	Paris
90133	Fred	Paris

The above is the detailed content of How to Find Exact Duplicate Records Across Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!