Claude,
I'd use a subquery to join the count of doctors to each hospital (code
below is for MS-SQL):
SELECT h.*, IsNull(d.DoctorCount, 0) AS DoctorCount
FROM hospitals h
LEFT JOIN (select hospitalId, count(hospitalId) AS DoctorCount
from doctors
group by hospitalid) AS d ON h.hospitalId =
d.hospitalId
I used a left join and an IsNull() statement because there theoretically
could be hospitals that have no doctors yet, but if every hospital has
at least one doctor you could use an inner join and eliminate the IsNull().
HTH,
Carl
On 11/18/2011 11:08 AM, =?ISO-8859-1?Q?Claude_Schn=E9egans wrote:
> Hi,
> Say I have a table with hospitals and a table with doctors.
> Every doctor belongs to an hospital (hospitalId)
>
> What I want is all columns for all hospitals plus the number of doctors
> attached to each hospital, in the same query.
> How would you do that?
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3425
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
