From: | "Marcel Loose" <loose(at)astron(dot)nl> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Newbie Query question |
Date: | 2004-03-23 09:56:12 |
Message-ID: | c3p1cv$2i55$1@news.wplus.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Marques | 2004-03-23 14:04:12 | Re: inverse of "day of year" |
Previous Message | Richard Huxton | 2004-03-23 08:08:56 | Re: function definition documentation |