Re: Counting foreign keys

From: <jdarrah_co_at_my-deja.com>
Date: Fri, 29 Dec 2000 20:27:40 GMT
Message-ID: <92is3l$tpe$1@nnrp1.deja.com>

If all you are trying to do is delete parents without children couldn't you use a NOT IN or NOT EXISTS query
something like

delete from table_a where table_a_pk not in ( SELECT table_b_fk from table_b)

you can also make this an outer join for (possible) better performance delete from table_a where table_a_pk = table_b_fk(+) and table_b_fk is null

In article <92iq6c$s5i$1_at_nnrp1.deja.com>, bhavinsh_at_my-deja.com wrote:
> Thanks, but how do I also get the counts of 0.
> This only returns counts > 0.
>
> Basically I'm trying to delete entries from tablea that
> don't have any child records in tableb.
>
> In article <3a4c6142_at_ns.baltros.ru>,
> "Sergey Gerasimov" <gsa_at_baltros.ru> wrote:
> > >
> > > TABLEA
> > > (
> > > pk
> > > )
> > >
> > > TABLEB
> > > (
> > > pk2
> > > fk REFERENCES TABLEA(pk)
> > > )
> >
> > select tablea.pk, count(tableb.pk2)
> > from tablea, tableb where tablea.pk = tableb.pk (+) group by
tablea.pk;
> >
> > --
> > Regards
> > Sergey Gerasimov
> > gsa_at_baltros.ru
> >
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 29 2000 - 14:27:40 CST