Multiple NOT distinct

Question

Hi,

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 
  * 
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?

Answer 2

join the unique values of B you determined with group by b and count > 1 back to the original table to retrieve the C values from the table.

Answer 3

Another way of returning the results you want would be this:

select *
from
    my_table
where 
    B in 
    (select B from my_table group by B having count(*) > 1)

Answer 4

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