How to Find Duplicate IDs with Different Values in Another Column?

Selecting Duplicate IDs with Varied Column Values

You have a table containing a unique ID (ARIDNR) and another column (LIEFNR) with varying values. The goal is to identify all rows where ARIDNR appears more than once but with different LIEFNR values.

To achieve this, consider the following query:

SELECT *
FROM Table
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM Table
    GROUP BY ARIDNR
    HAVING COUNT(DISTINCT LIEFNR) > 1
)

Breakdown:

• The outer SELECT statement retrieves all columns from the Table.
• The inner subquery selects unique ARIDNR values.
• The GROUP BY clause groups the results by ARIDNR.
• The HAVING clause filters the results to include ARIDNR values with more than one distinct LIEFNR.
• The outer WHERE clause checks if the ARIDNR of each row matches any of the ARIDNR values identified in the subquery.

As a result, this query should output the following table:

+------+------+
| ARIDNR | LIEFNR |
+------+------+
| 1     | A     |
| 1     | B     |
| 2     | A     |
| 2     | B     |
+------+------+

This table contains all rows where ARIDNR is duplicated and associated with different LIEFNR values.

The above is the detailed content of How to Find Duplicate IDs with Different Values in Another Column?. For more information, please follow other related articles on the PHP Chinese website!