<p>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.</p>

<p>In this case more than ever an example is worth thousands of words:</p>

<p>Table:</p>

<pre><code>A B C
1 x q
2 y w
3 y e
4 z r
5 z t
6 z y
</code></pre>

<p><em>SQL magic</em></p>

<p>Result:</p>

<pre><code>B C
y w
y e
z r
z t
z y
</code></pre>

<p>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.</p>

<p>Any help?</p>

<p>Thanks.</p>

```
```
select 
  * 
from 
  my_table t1, 
  my_table t2
where 
  t1.B = t2.B
and
  t1.C != t2.C

-- apparently you need to use <> instead of != in Access
-- Thanks, Dave!
```
```
Something like that?