Re: Finding uniques across a big join

On Wed, Nov 30, 2005 at 01:20:19PM -0500, John D. Burger wrote:
> >select p1.gazPlaceID
> > from gazPlaces as p1
> > join gazNamings as n1 using (gazPlaceID)
> > join gazContainers as c1 using (gazPlaceID)
> > group by p1.gazPlaceID, p1.featureType, n1.placeNameID,
> >c1.containerID
> > having count(*) = 1
>
> The problem is that then every row is unique, because gazPlaceID is a
> primary key. As far as I can see, I need to group on just the other
> three columns - they're the ones for which I'm interested in
> uniqueness.

AIUI, according to the JOIN conditions in the query you have
n1.gazPlaceID = c1.gazPlaceID = p1.gazPlaceID so grouping by one of
those shouldn't affect the query result.

Are the tables wide? Maybe you're losing a lot of time transferring
data you don't need. Other than that I can't think of any neat
tricks...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.