From: | "Diehl, Jeffrey" <jdiehl(at)sandia(dot)gov> |
---|---|
To: | "'Ken Corey '" <ken(at)kencorey(dot)com>, "Diehl, Jeffrey" <jdiehl(at)sandia(dot)gov> |
Cc: | "'pgsql-sql(at)postgresql(dot)org '" <pgsql-sql(at)postgresql(dot)org>, "'pgsql-general(at)postgresql(dot)org '" <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: Help with query. (*) |
Date: | 2001-01-17 20:17:57 |
Message-ID: | B51F0C636E578A4E832D3958690CD73E0130BD9F@es04snlnt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Welche | 2001-01-17 20:28:17 | Re: Re: MySQL file system |
Previous Message | Brian C. Doyle | 2001-01-17 19:53:51 | Sequence Help |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2001-01-17 20:57:49 | Re: RE: Help with query. (*) |
Previous Message | Ken Corey | 2001-01-17 19:34:05 | Re: Help with query. (*) |