Re: Help with query. (*)

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.

I'm not totally sure what you are trying to do here, but it looks like you
may be more interested in:

SELECT * FROM a, b WHERE A.x = B.x AND A.y=B.y AND A.z=B.z;

Are you familiar with table joins?

--
Adam Haberlach            |A cat spends her life conflicted between a
adam@newsnipple.com       |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500                 |profound desire to avoid getting wet.