We're using Oracle 8.0.5 and SQL Server 7. I know the sub select works
in Oracle 8.1.7 but... we're stuck with 8.0.5 for now ):
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<997908516.8057.0.pluto.d4ee154e_at_news.demon.nl>...
> "Anupriyo Chakravarti" <anu_chak_at_globalnoise.com> wrote in message
> news:c0fcc770.0108151112.20329432_at_posting.google.com...
> > This query works in MS SQL Server but not in Oracle 8. Any suggestions
> > how to achieve the samething in Oracle?
> >
> > SELECT TPARENT.id, TPARENT.name
> > (SELECT COUNT(*) FROM TPARENT, TCHILD WHERE TCHILD.parentid =
> > TPARENT.id) AS numchildren
> > FROM TPARENT
> >
> > Basically, I have two tables TPARENT and TCHILD. TCHILD has a FK
> > parentid to TPARENT.id. I want a list of all parents and the number of
> > children they have.
> >
> > Any insights would be appreciated.
> >
> > - Anupriyo
>
> Why not use
> select t.id, t.name, c.numchildren
> from
> (select tparent.id, tparent.name
> from tparent) t
> ,
> (select parentid
> , count(*) numchildren
> from tchild
> group by parentid
> ) c
> where t.parentid = c.parentid
>
> Also: please mention the version *exactly*, your construct -though probably
> less efficient in terms of performance- might work in 8.1.6 or 8.1.7
>
> Hth,
>
> Sybrand Bakker,
> Senior Oracle DBA
Received on Mon Aug 20 2001 - 13:50:27 CDT
