Re: NOT and AND problem - Mailing list pgsql-sql

----- Original Message -----
From: "Richard Jones" <r.d.jones@ed.ac.uk>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 17, 2003 5:29 PM
Subject: [SQL] NOT and AND problem


> Dear All,
>
> I am having some confusion over a query which is supposed to achieve the
> following:  To remove a record from a table if the one or both of the
> columns containing references to other tables no longer point to table
rows
> which still exist.  There are good reasons why I cannot use foreign keys
to
> maintain referential integrity, but I will not go into them, but they give
> rise to the need to "clean-up" my database table from time to time.  The
> query that I have had most success with looks like this:
>
> DELETE FROM myTable
> WHERE (NOT myTable.item_id = item.item_id)
> AND (NOT myTable.group_id = ep.group_id);
>
> Which is odd, because logically it shouldn't work.  What I find with the
> above queries is that as follows:
>
> let myTable.item_id = item.item_id be A
> let myTable.group_id = ep.group_id be B
>
> The derived and actual truth tables for the results of the where clause
> follow:
>
> Derived:
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 0
> 0 | 1 | 0
> 0 | 0 | 1
>
> Actual:
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 0
> 0 | 1 | 1
> 0 | 0 | 1
>
> This makes no sense to me, as effectively rows 2 and 3 of the Actual
results
> truth table are the same (unless there's some subtle difference with
regards
> to the order of the statements, otherwise just substitute A for B and vice
> versa).
>
> The result that I actually want from the operation is this:
>
> A | B | Result
> 1 | 1 | 0
> 1 | 0 | 1
> 0 | 1 | 1
> 0 | 0 | 1
>
> which would suggest a query like:
>
> DELETE FROM myTable
> WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id =
> ep.group_id);

If u want this u can obtain by
DELETE FROM myTable
WHERE (NOT(myTable.item_id = item.item_id )) OR (NOT(myTable.group_id =
ep.group_id));

You can write ! instead of NOT.
Look at the operations precedence. The NOT might get executed before "=".


>
> which ought to provide the above output.  Instead, using this query, the
> output I get is as follows:
>
> A | B | Result
> 1 | 1 | 1
> 1 | 0 | 1
> 0 | 1 | 1
> 0 | 0 | 1
>
> I can only conclude that Postgres is doing something with regards to the
> other two tables which I am unaware of.  Can anyone help me understand
what
> is going on?  Any suggestions gratefully received.
>
> Cheers
>
> Richard
>
>
> Richard Jones
> -----------------------
> Systems Developer
> Theses Alive! - www.thesesalive.ac.uk
> Edinburgh University Library
> r.d.jones@ed.ac.uk
> 0131 651 1611
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)