Thread: Problem with boolean WHERE-clause

Hello everyone,

  I am a bit confused with a boolean equation
in the where-clause of an sql statement. The
scenario is as follows:

I have three tables, lets call them a,b, and c.
There are cross-reference tables between them:
ab, ac. Now I want to have a query that lists
all a-rows where the id of b and/or c is known.

But somehow my statement doesn't seem to work
(foreign keys ommitted for simplicity):

crm=# create table a ( id int );
CREATE
crm=# create table b ( id int );
CREATE
crm=# create table c ( id int );
CREATE
crm=# create table ab ( id1 int, id2 int );
CREATE
crm=# create table ac ( id1 int, id2 int );
CREATE
crm=# insert into a values(1);
INSERT 64600 1
crm=# insert into b values(1);
INSERT 64601 1
crm=# -- no value in c
crm=# insert into ab values(1,1);
INSERT 64602 1
crm=# select * from a where (a.id = ab.id1 and ab.id2 = 1);
NOTICE:  Adding missing FROM-clause entry for table "ab"
 id
----
  1
(1 row)
crm=# -- this works fine
crm=# select * from a where ((a.id = ab.id1 and ab.id2 = 1) or (a.id = ac.id1 and ac.id2 = 1));
NOTICE:  Adding missing FROM-clause entry for table "ab"
NOTICE:  Adding missing FROM-clause entry for table "ac"
 id
----
(0 rows)
crm=# -- why doesn't this work? the first part is true, and the second
crm=# -- part is connected with OR?

Any pointers would be appreciated. I'm not subscribed to the list
but I read the archives; yet if you don't mind, please forward
any answer to my e-mail chris@gidayu.mud.de

Thanks in advance,
- Chris

--
Christian Loth
Coder of 'Project Gidayu'
Computer Science Student, University of Dortmund
chris@gidayu.mud.de - http://gidayu.mud.de

On Thu, 2 Jan 2003, Christian Loth wrote:

> But somehow my statement doesn't seem to work
> (foreign keys ommitted for simplicity):
>
> crm=# create table a ( id int );
> CREATE
> crm=# create table b ( id int );
> CREATE
> crm=# create table c ( id int );
> CREATE
> crm=# create table ab ( id1 int, id2 int );
> CREATE
> crm=# create table ac ( id1 int, id2 int );
> CREATE
> crm=# insert into a values(1);
> INSERT 64600 1
> crm=# insert into b values(1);
> INSERT 64601 1
> crm=# -- no value in c
> crm=# insert into ab values(1,1);
> INSERT 64602 1
> crm=# select * from a where (a.id = ab.id1 and ab.id2 = 1);
> NOTICE:  Adding missing FROM-clause entry for table "ab"
>  id
> ----
>   1
> (1 row)
> crm=# -- this works fine
> crm=# select * from a where ((a.id = ab.id1 and ab.id2 = 1) or (a.id = ac.id1 and ac.id2 = 1));
> NOTICE:  Adding missing FROM-clause entry for table "ab"
> NOTICE:  Adding missing FROM-clause entry for table "ac"
>  id
> ----
> (0 rows)
> crm=# -- why doesn't this work? the first part is true, and the second
> crm=# -- part is connected with OR?

Read the notices, it's adding "ab" and "ac" to the from clause,
so it is as if you had done
select * from a, ab, ac where ...

If there are no rows in ac, the join returns no rows so there's no rows
for the where clause to act upon.  Technically the query is invalid,
but postgres is trying to guess what you wanted (which I think was
closer to:
select * from a where exists (select * from ab where a.id=ab.id1 and
 ab.id2=1) or exists (select * from ac where a.id=ac.id1 and ac.id2=1);
)