How can I fetch duplicate rows-CodePudding

Assuming I have a table like:

ID	valA	valB	valC
1	21	aaaaa	NYC
4	12	bbbbb	NYC
2	31	ddddd	LA
2	23	eeeee	LA
2	41	fffff	NE
3	23	ggggg	JE
3	44	hhhhh	KA

I want to have all rows that have duplicate of valC. So, as a result I would like to have this:

ID	valA	valB	valC
1	21	aaaaa	NYC
4	12	bbbbb	NYC
2	31	ddddd	LA
2	23	eeeee	LA

I tried something like this:

SELECT DISTINCT *, count(valC) 
FROM table A
INNER JOIN table B ON A.valC = B.valC
WHERE A.valC = B.valC AND valC > 1;

I am pretty new on SQL so I would appreciate any suggestions.

CodePudding user response：

An easy way to use Subquery

This example is working in MySQL

SELECT * 
FROM table_name
WHERE valC IN ( SELECT valC 
                FROM table_name 
                GROUP BY valC 
                HAVING COUNT(valC) > 1);