Not tested this but:
assuming: names are same for identical id's
SELECT p.id, p.name, count(*)
FROM TPARENT p,TCHILD c
WHERE p.id = c.id
GROUP BY p.id,p.name
anu_chak_at_globalnoise.com (Anupriyo Chakravarti) 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
Received on Thu Aug 16 2001 - 03:31:06 CDT
