SQL unique combinations?

[Thomas McK]

Hi all, I'm not sure how to get around this, I was hoping someone
could provide me with some help. Let's take this simple data set of 2
columns in a table:

Tom - id1
Bob - id2
Jane - id1
John - id8
Fred - id2
John - id8
Bob - id1

Using SQL, I'm trying to return the ones that are *not* unique. I
don't really know if Tom is actually id1 or if Jane is. Since they
both share an ID, I want to return them (as the errors!). Since Bob
has 2 different IDs and ID2 can also belong to Fred, I want to return
all of those. (etc). The only one not coming back should be
John-id8, because he's the one with the unique ID (even if he's listed
twice).

These names and IDs are two different columns in the same table, so no
inter-table relationships involved here!

Can anyone possibly offer assistance? Much thanks!

T-McK

[Eric Schittlipz]

"Thomas McK" <ed********@yah oo.com> wrote in message
news:85******** *************** **@posting.goog le.com...

Hi all, I'm not sure how to get around this, I was hoping someone
could provide me with some help. Let's take this simple data set of 2
columns in a table:

Tom - id1
Bob - id2
Jane - id1
John - id8
Fred - id2
John - id8
Bob - id1

Using SQL, I'm trying to return the ones that are *not* unique. I
don't really know if Tom is actually id1 or if Jane is. Since they
both share an ID, I want to return them (as the errors!). Since Bob
has 2 different IDs and ID2 can also belong to Fred, I want to return
all of those. (etc). The only one not coming back should be
John-id8, because he's the one with the unique ID (even if he's listed
twice).

These names and IDs are two different columns in the same table, so no
inter-table relationships involved here!

Can anyone possibly offer assistance? Much thanks!

T-McK

We don't have your table and field names, but you should be able to adjust
the following accordingly:

SELECT ConID, ConName FROM tblContacts WHERE ConID IN
(SELECT X.ConID FROM
(SELECT ConID FROM tblContacts GROUP BY ConID, ConName) AS X
GROUP BY X.ConID HAVING Count(*)>1)
ORDER BY ConID, ConName

[David Schofield]

On 28 Sep 2004 12:39:01 -0700, ed********@yaho o.com (Thomas McK)
wrote:

Hi all, I'm not sure how to get around this, I was hoping someone
could provide me with some help. Let's take this simple data set of 2
columns in a table:

Tom - id1
Bob - id2
Jane - id1
John - id8
Fred - id2
John - id8
Bob - id1

Using SQL, I'm trying to return the ones that are *not* unique. I
don't really know if Tom is actually id1 or if Jane is. Since they
both share an ID, I want to return them (as the errors!). Since Bob
has 2 different IDs and ID2 can also belong to Fred, I want to return
all of those. (etc). The only one not coming back should be
John-id8, because he's the one with the unique ID (even if he's listed
twice).

These names and IDs are two different columns in the same table, so no
inter-table relationships involved here!

Can anyone possibly offer assistance? Much thanks!

T-McK

Hi
Here is a brute-force method, I am sure someone has a simple and or
elegant one!

query2 = SELECT DISTINCT Table2.name, Table2.id FROM Table2;

singleids =
SELECT id FROM Query2 GROUP BY id HAVING Count(Query2.na me)=1;

singlenames =
SELECT name FROM Query2 GROUP BY name HAVING Count(Query2.id )=1;

unique =
SELECT DISTINCT Table2.*
FROM (Table2 INNER JOIN singlenames ON Table2.name = singlenames.nam e)
INNER JOIN singleids ON Table2.id = singleids.id;

David

[Thomas McK]

That was very, very helpful! Both of you presented different ways to
do it, and certainly opened my eyes to new solutions. Thanks!!


d.************* **@blueyonder.c o.uk (David Schofield) wrote in message news:<4159e768. 439640759@local host>...

On 28 Sep 2004 12:39:01 -0700, ed********@yaho o.com (Thomas McK)
wrote:

Hi all, I'm not sure how to get around this, I was hoping someone
could provide me with some help. Let's take this simple data set of 2
columns in a table:

Tom - id1
Bob - id2
Jane - id1
John - id8
Fred - id2
John - id8
Bob - id1

Using SQL, I'm trying to return the ones that are *not* unique. I
don't really know if Tom is actually id1 or if Jane is. Since they
both share an ID, I want to return them (as the errors!). Since Bob
has 2 different IDs and ID2 can also belong to Fred, I want to return
all of those. (etc). The only one not coming back should be
John-id8, because he's the one with the unique ID (even if he's listed
twice).

These names and IDs are two different columns in the same table, so no
inter-table relationships involved here!

Can anyone possibly offer assistance? Much thanks!

T-McK

Hi
Here is a brute-force method, I am sure someone has a simple and or
elegant one!

query2 = SELECT DISTINCT Table2.name, Table2.id FROM Table2;

singleids =
SELECT id FROM Query2 GROUP BY id HAVING Count(Query2.na me)=1;

singlenames =
SELECT name FROM Query2 GROUP BY name HAVING Count(Query2.id )=1;

unique =
SELECT DISTINCT Table2.*
FROM (Table2 INNER JOIN singlenames ON Table2.name = singlenames.nam e)
INNER JOIN singleids ON Table2.id = singleids.id;

David