SELECT P.Title, P.Name, Count(C.ID) AS CountCars, Count(H.ID) AS
CountHouses, Count(B.ID) AS CountBoats
FROM People P
LEFT OUTER JOIN PeopleCars C
ON P.ID = C.PeopleID
LEFT OUTER JOIN PeopleHouses H
ON P.ID = H.PeopleID
LEFT OUTER JOIN PeopleBoats B
ON P.ID = B.PeopleID
I've never done left outer joins on more than 2 tables, so this may or may
not work.
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: <[EMAIL PROTECTED]>
<allan@redpro cc:
.net> Subject: Re: [ cf-dev ] SQL question -
brain a blank!
09/19/02
12:13 PM
Please
respond to
dev
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]
