From: Adam Haberlach <adam@newsnipple.com>
To: Diehl, Jeffrey <jdiehl@sandia.gov>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: Help with query. (*)
Date: Thu, 18 Jan 2001 09:37:13 -0800
Message-ID: <20010118093713.B13028@newsnipple.com> (raw)
In-Reply-To: <B51F0C636E578A4E832D3958690CD73E0130BD9A@es04snlnt>
References: <B51F0C636E578A4E832D3958690CD73E0130BD9A@es04snlnt>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adam@newsnipple.com, jdiehl@sandia.gov, pgsql-general@postgresql.org
Subject: Re: Help with query. (*)
In-Reply-To: <20010118093713.B13028@newsnipple.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox