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]

Re: SQL unique combinations?

"Thomas McK" <ediblepony@yah oo.com> wrote in message
news:85cff1ce.0 409281139.e81df ab@posting.goog le.com...[color=blue]
> 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[/color]


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]

Re: SQL unique combinations?

On 28 Sep 2004 12:39:01 -0700, ediblepony@yaho o.com (Thomas McK)
wrote:
[color=blue]
>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[/color]

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]

Re: SQL unique combinations?

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




d.REMOVEschofie ld@blueyonder.c o.uk (David Schofield) wrote in message news:<4159e768. 439640759@local host>...[color=blue]
> On 28 Sep 2004 12:39:01 -0700, ediblepony@yaho o.com (Thomas McK)
> wrote:
>[color=green]
> >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[/color]
>
> 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[/color]