From: "Marcel Loose" <loose@astron.nl>
Subject: Newbie Query question
Date: Tue, 23 Mar 2004 10:56:12 +0100
Organization: Webplus NEWS server
Lines: 28
Message-ID: <c3p1cv$2i55$1@news.wplus.net>
To: pgsql-sql@postgresql.org

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)