Problem with boolean WHERE-clause

From: Christian Loth <chris(at)gidayu(dot)mud(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with boolean WHERE-clause
Date: 2003-01-01 23:18:46
Message-ID: 20030102001846.B31528@60-4.stw.uni-duisburg.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)gidayu(dot)mud(dot)de

Thanks in advance,
- Chris

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jochem van Dieten 2003-01-01 23:32:44 Re: creating languages in the pg_catalog schema?
Previous Message mark carew 2003-01-01 21:41:41 Re: Thanks! Got it! All's OK now! was: Is postgresql download still working?