Problem with boolean WHERE-clause 
Author Message
 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

Thanks in advance,
- Chris

--
Christian Loth
Coder of 'Project Gidayu'
Computer Science Student, University of Dortmund

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Tue, 21 Jun 2005 09:33:56 GMT
 Problem with boolean WHERE-clause

Quote:

> 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);
)

---------------------------(end of broadcast)---------------------------



Tue, 21 Jun 2005 11:05:08 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Problem with translate function in whereclause.

2. Boolean expression in select clause

3. Boolean complement of a pair of OR clause

4. Can you use Date_time timestamp columns for Whereclause

5. TSQL 7.0, More limiting whereclause reduces # returned rows

6. whereclause - colname in (null,"s")

7. docmd.openform and WhereClause?

8. left outer joins with a whereclause seem to not work correctly

9. whereclause and timestamp

10. Problems with JDBC and a DISTINCT Boolean Type in a Row Type

11. Boolean Function Problem.

12. Boolean Field Problem


 
Powered by phpBB® Forum Software