From: <terry@ashtonwoodshomes.com>
To: "'Richard Jones'" <r.d.jones@ed.ac.uk>,
	<pgsql-sql@postgresql.org>
Subject: Re: NOT and  AND problem
Date: Thu, 17 Jul 2003 10:52:25 -0400
Message-ID: <010e01c34c73$097ad220$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <010b01c34c6f$ca5bcac0$2766f30a@development.greatgulfhomes.com>
Importance: Normal

And after reading Viorel's response I realized that you wanted the record
deleted if EITHER "foreign key" was broken, not just if BOTH fkeys are
broken, therefore simply change the AND to an OR:

DELETE FROM myTable
WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
  OR NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id);


Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of
> terry@ashtonwoodshomes.com
> Sent: Thursday, July 17, 2003 10:29 AM
> To: 'Richard Jones'; pgsql-sql@postgresql.org
> Subject: Re: [SQL] NOT and AND problem
>
>
> DELETE FROM myTable
> WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
> AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id);
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
> Fax: (416) 441-9085
>
>
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Jones
> > Sent: Thursday, July 17, 2003 10:29 AM
> > To: pgsql-sql@postgresql.org
> > 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);
> >
> > 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)
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>