Date: Thu, 18 Jan 2001 14:57:32 +0000
From: Patrick Welche <prlw1@newn.cam.ac.uk>
To: "Diehl, Jeffrey" <jdiehl@sandia.gov>
Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Help with query.   (*)
Message-ID: <20010118145732.F29761@quartz.newn.cam.ac.uk>
Reply-To: prlw1@cam.ac.uk
References: <B51F0C636E578A4E832D3958690CD73E0130BD9A@es04snlnt>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2i
In-Reply-To: <B51F0C636E578A4E832D3958690CD73E0130BD9A@es04snlnt>;
	from jdiehl@sandia.gov on Tue, Jan 16, 2001 at 01:42:45PM -0700

On Tue, Jan 16, 2001 at 01:42:45PM -0700, 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.

Guessing - my system isn't in a state to test just at the minute - is it

select A.* from A,B
 where A.x=B.x
   and A.y=B.y
   and A.z=B.z
 limit 100

that you want?