Re: Finding uniques across a big join

Scott Marlowe wrote:

>> Won't this be a massive cross product of all pkey pairs that have the
>> same field values?
>
> Yes, assuming there are a lot of them.  OTOH, if there are only a few
> duplicates you're looking for...

I'm not looking for duplicates, I'm looking for uniques - note the
Subject line :).  Here's an analogous problem:  everybody in the room
has eye color, hair color, and skin color.  I want to find the people
whose particular three-way combination is unique - no one else has that
eye-hair-skin combo.  The analogue to my current query is then like
this:

   select p1.personID, p1.eyeColor, p1.hairColor, p1.skinColor
     from persons as p1
     join
     (select p2.eyeColor, p2.hairColor, p2.skinColor
       from persons as p2
       group by p2.eyeColor, p2.hairColor, p2.skinColor
       having count(*) = 1)
     using (eyeColor, hairColor, skinColor);

The inner select finds the unique combinations, the outer one goes back
and finds the peopleID corresponding to each unique combo.  And the
persons table is actually a view on a big three-way join.

Jim Nasby wrote:

> Someone else suggested adding gazPlaceID to the GROUP BY; I definately
> think you should do that.

That changes the semantics of what I want.  If I group by personID
above, then every FOUR-way combo is of course unique.  What I'd like to
do is group by the three attributes, and select for personID as well.
But of course you can't select for columns you haven't grouped by.

Sorry, I can't think of any other ways to explain what I'm doing.  But
thank you for your replies.

- John Burger
   MITRE