Simple GROUP BY Question

[Jim Clark]

Suppose the following tables:
parent
------
parentid
address
phone

[David Portas]

If Parentid is declared UNIQUE or PRIMARY KEY then just list all the
columns:

SELECT P.parentid, P.address, P.phone,
COUNT(*)
FROM parent AS P
JOIN child AS C
ON P.parentid = C.parentid
GROUP BY P.parentid, P.address, P.phone

I'm certain this will perform better than the subquery alternative. Note
that you should avoid using SELECT * in production code.

If ParentID isn't unique then you could do the following:

SELECT P.parentid, P.address, P.phone, C.cnt
FROM Parent AS P
JOIN
(SELECT parentid, COUNT(*)
FROM Child
GROUP BY parentid) AS C(parentid,cnt)
ON P.parentid = C.parentid

--
David Portas
------------
Please reply only to the newsgroup
--