Re: SQLServer to Oracle

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 15 Aug 2001 22:48:26 +0200
Message-ID: <997908516.8057.0.pluto.d4ee154e@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 Wed Aug 15 2001 - 15:48:26 CDT