CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 3

SQL: find records in Column A with duplicates in Column B

I'm wondering if you could help me on an sql problem I have.

my table looks like

ID    ColumnA     ColumnB
1       123          A
2       123          A
3       123          B
4       456          A
5       456          B
6       456          B
7       789          A
8       789          B
9       789          C

I'd like to identify all records of ColumnA which have duplicates in ColumnB, so the query should return

ID    ColumnA     ColumnB
1       123          A
2       123          A
5       456          B
6       456          B

Thanks a lot in advance. Cheers

Upvotes: 0

Views: 28

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

You could use EXISTS:

SELECT *
FROM tab t1
WHERE EXISTS (SELECT 1 
             FROM tab t2 
             WHERE t1.ColumnA=t2.ColumnA AND t1.ColumnB = t2.ColumnB
               AND t1.id <> t2.id);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

If you have analytic functions available (MySQL 8.x and later), then use them:

SELECT ID, ColumnA, ColumnB
FROM
(
    SELECT *, COUNT(*) OVER (PARTITION BY ColumnA, ColumnB) cnt
    FROM yourTable
) t
WHERE cnt > 1;

If you don't have analytic functions, then we have to do a bit more work:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT ColumnA, ColumnB, COUNT(*) AS cnt
    FROM yourTable
    GROUP BY ColumnA, ColumnB
) t2
    ON t1.ColumnA = t2.ColumnA AND t1.ColumnB = t2.ColumnB AND t2.cnt > 1;

Upvotes: 1

Related Questions