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)