From: Marcel Loose <loose@astron.nl>
To: pgsql-sql@postgresql.org
Subject: Newbie Query question
Date: Tue, 23 Mar 2004 10:56:12 +0100
Message-ID: <c3p1cv$2i55$1@news.wplus.net> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: loose@astron.nl
Subject: Re: Newbie Query question
In-Reply-To: <c3p1cv$2i55$1@news.wplus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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