Home >Database >Mysql Tutorial >How to Find Duplicate IDs with Different Values in Another Column?

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

Barbara Streisand
Barbara StreisandOriginal
2024-12-31 02:43:13329browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn