Re: query help

On Feb 21, 9:48 am, "tamkat..._at_gmail.com" <tamkat..._at_gmail.com> wrote:
> On 21 Feb., 15:02, "www.douglassdavis.com"
>
>
>
> <douglass_da..._at_earthlink.net> wrote:
> > Say I have two tables:
>
> > table_a
> > ----------
> > a_id (primary key)
> > b_id
>
> > table_b
> > ----------
> > b_id (primary key)
> > name
>
> > there is a one to many mapping between rows in table b and rows in
> > table a.
>
> > Say I had an Id of a row in table a an (a_id, say 5). Now, what I
> > want to do, is delete the row in table_a (easy enough), but I also
> > want to delete related rows in table_b, if they are ONLY related to
> > the a_id of 5.
>
> > In other words, I want to delete the row from table b (related to
> > a_id), but I don't
> > want to delete the row from table b that may be in use by another row
> > in
> > table a.
>
> > How could I construct a query (or 2) to do that?
>
> Hi,
>
> Here's 4 ways to do the deletion from b, they combine the use of IN
> and EXISTS. The achieve the same logically, but they may perform very
> differently depending a.o. on the size of table A and B.
>
> delete from b
> where b_id in (select b_id
> from a
> where a_id = 5 )
> and not exists (select 1 from a where b_id = b.b_id and a_id != 5);
>
> delete from b
> where b_id in (select b_id
> from a
> where a_id = 5 )
> and b_id not in (select b_id from a where a_id != 5);
>
> delete from b
> where exists (select 1 from a where b_id = b.b_id and a_id = 5)
> and not exists (select 1 from a where b_id = b.b_id and a_id != 5);
>
> delete from b
> where exists (select 1 from a where b_id = b.b_id and a_id = 5)
> and b_id not in (select b_id from a where a_id != 5);
>
> - Kenneth Koenraadt

thanks. Received on Thu Feb 21 2008 - 11:47:42 CST