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