how about
select (select count(ID) from PeopleCars where peopleID = #ID#) AS
countcars,
(select cound(ID) from PeopleBoats where peopleID = #ID#) AS countboats,
(select count(ID) from PeopleHouses where peopleID = #ID#) AS counthouses,
people.name
from people
where peopleID = #ID#
-----Original Message-----
From: Allan Cliff RedPro [mailto:[EMAIL PROTECTED]]
Sent: 19 September 2002 12:13
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] SQL question - brain a blank!
Thanks but I was hoping for 1 query.
----- Original Message -----
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 19, 2002 1:13 PM
Subject: Re: [ cf-dev ] SQL question - brain a blank!
SELECT Title, Name
FROM People
WHERE ID = 1
SELECT Count(ID) AS CountCars
FROM PeopleCars
WHERE PeopleID = 1
SELECT Count(ID) AS CountBoats
FROM PeopleBoats
WHERE PeopleID = 1
SELECT Count(ID) AS CountHouses
FROM PeopleHouses
WHERE PeopleID = 1
Could also be done as one query, but this way will work. If you're
outputting many rows from People, not just the one, put the 3 Count
queries
inside the cfoutput loop.
Duncan Cumming
IT Manager
http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600
Creative solutions in a technical world
----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------
"Allan Cliff
RedPro" To: "CF - List"
<[EMAIL PROTECTED]>
<allan@redpro cc:
.net> Subject: [ cf-dev ] SQL
question - brain a blank!
09/19/02
11:57 AM
Please
respond to
dev
I have 4 tables.
people, peoplecars, peopleboats. peoplehouses.
I want to do a query on people which gives me the following list.
Person - CountCars - CountBoats - CountHouses
Mr Fred Bloggs - 10 cars - 0 boats - 1 house.
Thanks
Allan
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
