Help with query. (*)

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

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

Mike,

In that case, you want to use this construction:

DELETE FROM a
WHERE EXISTS (
SELECT 1 FROM b
WHERE b.1 = a.1
AND b.2 = a.2
AND b.3 = a.3 );

Of course, a good primary keying system would make this somewhat less
complex ...

-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco

"Diehl, Jeffrey" <jdiehl@sandia.gov> writes:

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. ;^)

If you don't mind being nonstandard, you could still do it in join
style:

DELETE FROM a WHERE a.x = b.x AND blah blah blah ...

Under Postgres this will form a join between A and B same as if you'd
said SELECT FROM a,b WHERE a.x = b.x etc, and then delete the rows of
A that are matched in the join.

If you want to be bog-SQL-standard then you have to use the WHERE EXISTS
construct that Josh mentioned. Unfortunately, that's likely to be a
good deal slower (for large tables) under current releases of Postgres.
We have hopes of bringing the performance of the EXISTS variant up to
something close to the explicit join, but it's a version or two away
yet.

regards, tom lane

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?

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.

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. (*)