RE: Help with query. (*)
От | Diehl, Jeffrey |
---|---|
Тема | RE: Help with query. (*) |
Дата | |
Msg-id | B51F0C636E578A4E832D3958690CD73E0130BD9F@es04snlnt обсуждение исходный текст |
Ответ на | Help with query. (*) ("Diehl, Jeffrey" <jdiehl@sandia.gov>) |
Ответы |
Re: RE: Help with query. (*)
|
Список | 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@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.
В списке pgsql-sql по дате отправления: