----- Original Message -----
From: "Richard Jones" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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
> [EMAIL PROTECTED]
> 0131 651 1611
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
