Обсуждение: Help with query. (*)

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.

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.

[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.

Well, actually, I want to eventually delete the records from A if there is
an entry in B.  That's why I am trying to use such a screwed up query. ;^)

Thanx,
Mike Diehl.

-----Original Message-----
From: Ken Corey
To: Diehl, Jeffrey
Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
Sent: 1/17/01 12:34 PM
Subject: Re: Help with query.   (*)
Importance: High

[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.

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?

Read the docs on "exists" that should be what you are looking for.

--rob

----- Original Message -----
From: "Diehl, Jeffrey" <jdiehl@sandia.gov>
To: <pgsql-sql@postgresql.org>; <pgsql-general@postgresql.org>
Cc: "Diehl, Jeffrey" <jdiehl@sandia.gov>
Sent: Tuesday, January 16, 2001 3:42 PM
Subject: Help with query. (*)


> 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.
>
>