Thread: Newbie Query question

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)

> 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.