The Thank You was for Russ! No Group By was needed.
Allan ----- Original Message ----- From: Russ 'Snake' Michaels To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 2:14 PM Subject: RE: [ cf-dev ] SQL question - brain a blank! 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]
