One way:
Select hospital.<columns>,
(select count(*)
From doctors
Where Hospital.Hospitalid = doctors.hospitalid) as DoctorCount
I am sure there are other ways with joins but I find this method works
fairly well and is easier for myself and others to understand. I want code
that is usable and readable rather 100% optimal. Human time is expensive,
computer resources are cheap.
Ray Thompson
Tau Beta Pi Headquarters
865-546-4578
www.tbp.org
-----Original Message-----
From: Claude Schnéegans <schneeg...@internetique.com>
[mailto:=?ISO-8859-1?Q?Claude_Schn=E9egans <schneegans@interneti=71?=
=?ISO-8859-1?Q?ue.com=3E?=]
Sent: Friday, November 18, 2011 2:09 PM
To: sql
Subject: use of count function
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:3424
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
