left joins are very resource intensive. should always be avoided if possible.
-----Original Message----- From: Tim Blair [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 12:32 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] SQL question - brain a blank! > Person - CountCars - CountBoats - CountHouses > Mr Fred Bloggs - 10 cars - 0 boats - 1 house. SELECT pe.person AS Person, pc.COUNT(*) AS CountCars, pb.COUNT(*) AS CountBoats, ph.COUNT(*) AS CountHouses FROM people AS pe LEFT JOIN peoplecars AS pc ON pe.personid = pc.personid LEFT JOIN peopleboats AS pb ON pe.personid = pb.personid LEFT JOIN peoplehouses AS ph ON pe.personid = ph.personid That should do it. Tim. ------------------------------------------------------- Tim Blair Web Application Engineer, Rawnet Limited Direct Phone : +44 (0) 1344 393 441 Switchboard : +44 (0) 1344 393 040 ------------------------------------------------------- rawnet ltd Atrium Court Bracknell [EMAIL PROTECTED] Berkshire Tel : +44 (0) 1344 393 040 RG12 1BW, UK http://www.rawnet.com ------------------------------------------------------- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of rawnet limited, unless otherwise explicitly and independently indicated by an authorised representative of rawnet limited. ------------------------------------------------------- > -----Original Message----- > From: Allan Cliff RedPro [mailto:[EMAIL PROTECTED]] > Sent: 19 September 2002 11:58 > To: CF - List > Subject: [ cf-dev ] SQL question - brain a blank! > > > 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]
