sql - Multiple NOT distinct

Question

I've got an MS access database and I would need to create an SQL query that allows me to select all the not distinct entries in one column while still keeping all the values.

In this case more than ever an example is worth thousands of words:

Table:

A B C
1 x q
2 y w
3 y e
4 z r
5 z t
6 z y

SQL magic

Result:

B C
y w
y e
z r
z t
z y

Basically it removes all unique values of column B but keeps the multiple rows of the data kept. I can "group by b" and then "count>1" to get the not distinct but the result will only list one row of B not the 2 or more that I need.

Any help?

Thanks.

Answer 1

Select B, C
From Table
Where B In
    (Select B From Table
     Group By B
     Having Count(*) > 1)