Обсуждение: 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. > >