CODEWITHSUNDEEP
sqlms-accessdistinct
tbate
tbate

Reputation:

Multiple NOT distinct

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.

Upvotes: 14

Views: 23580

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146429

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

Upvotes: 27

a2800276
a2800276

Reputation: 3328

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?

Upvotes: 3

Dave DuPlantis
Dave DuPlantis

Reputation: 6572

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)

Upvotes: 7

Paul Morgan
Paul Morgan

Reputation: 32518

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.

Upvotes: 1

Related Questions