CODEWITHSUNDEEP
mysqldatabase
Vũ Đình Hùng
Vũ Đình Hùng

Reputation: 13

Show all rows in MySQL that contain the same value (2 column filter)

I have a table like that

| c1 | c2 |
+----+----+
| a  | 2  |
| c  | 1  |
| c  | 2  |
| d  | 3  |
| a  | 2  |
| c  | 2  |
| c  | 4  |
| d  | 2  |

I want to select what value from c1 that have same c2. I tried this

SELECT c2, GROUP_CONCAT(DISTINCT c1)
FROM table
group by c2;

and i got

| c2 | c1   |
+----+------+
| 1  | c    |
| 2  | a,c,d| <==
| 3  | d    |
| 4  | c    |

How to select that show only rows that have more than 1 value at c1 as row 2?

Upvotes: 1

Views: 904

Answers (1)

David
David

Reputation: 1908

SELECT c2, GROUP_CONCAT(DISTINCT c1)
FROM table
group by c2 HAVING count(distinct c1)>1

Upvotes: 2

Related Questions