pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Newbie Query question 2+ messages / 2 participants [nested] [flat]
* Newbie Query question @ 2004-03-23 09:56 Marcel Loose <loose@astron.nl> 0 siblings, 1 reply; 2+ messages in thread From: Marcel Loose @ 2004-03-23 09:56 UTC (permalink / raw) To: pgsql-sql Hi all, I have the following problem which I will illustrate with a simplified example. I have two tables A and B. Both tables contain three columns named "objid", "owner", and "val" all of type integer. I want to select all records in A for which A.val=0 and all records in B for which both B.val=0 and B.owner=A.objid. I thought that the following query would work: SELECT * FROM A,B WHERE (A.VAL = 0) OR (B.VAL = 0 AND B.OWNER = A.OBJID); However, this query does not give me the result I expected. It appears that the database engine first calculates the cartesian product of the tables A and B and then evaluates the query. Hence, I get multiple matches for A.VAL=0 (N times the number of matching records in table A, where N is the number of records in table B). I had hoped I could somehow coerce the database engine to only use table A when evaluating the first part of the query, and use both tables A and B when evaluating the second part of the query. Is there any way to do this, other than using UNION?? Kind regards, Marcel Loose (mailto loose at astron dot nl) ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Newbie Query question @ 2004-03-26 14:49 Rod Taylor <pg@rbt.ca> parent: Marcel Loose <loose@astron.nl> 0 siblings, 0 replies; 2+ messages in thread From: Rod Taylor @ 2004-03-26 14:49 UTC (permalink / raw) To: Marcel Loose <loose@astron.nl>; +Cc: pgsql-sql > However, this query does not give me the result I expected. It appears that > the database engine first calculates the cartesian product of the tables A > and B and then evaluates the query. Hence, I get multiple matches for Yup.. WHERE filters the results of the join. > Is there any way to do this, other than using UNION?? This is what you want. I think you could come out with what you want in other ways, but this is by far the most appropriate. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-03-26 14:49 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-03-23 09:56 Newbie Query question Marcel Loose <loose@astron.nl> 2004-03-26 14:49 ` Rod Taylor <pg@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox