Message-ID: <3A65F3AD.7FDA0A49@kencorey.com>
Date: Wed, 17 Jan 2001 19:34:05 +0000
From: Ken Corey <ken@kencorey.com>
Reply-To: ken@kencorey.com
MIME-Version: 1.0
To: "Diehl, Jeffrey" <jdiehl@sandia.gov>
CC: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Help with query.   (*)
References: <B51F0C636E578A4E832D3958690CD73E0130BD9A@es04snlnt>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

[NOTE: I'm a pgsql newbie myself.  Take this reply with a large-ish
grain of salt!)

Shouldn't it be something straightforward like:
select a.a, a.b, a.c, ...
  from a a, 
       b b
 where a.x = b.x,
       and a.y = b.y, ...   (I'd watch out for too many clauses here...
				if you've got a lot of clauses, you're probably not normalized 
				as much as you should be.)

If you have indexes on the relevant fields, you shouldn't get a table
scan and this should return rather quickly, right?

-Ken


"Diehl, Jeffrey" wrote:
> 
> I'm having difficulty writing a query which I really can't live without...
> 
> I need to get a list of records from table A for which there are
> corresponding records in table B.  I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long.  For example:
> 
> select * from A
>         where 1=1
>         intersect select * from A where
>         B.x=A.x
>         and A.y=B.y
>         and A.z=B.z
>         limit 100
> 
> I need the most efficient method possible; my A tables have upward of 5
> Million records.  The B table, btw, only has about 100 records.
> 
> Any help will be most appreciated.