Toby I'd suggest that you need to do a left outer join on the client_id from the sub query. That should return NULL where the multiple count does not exist and depending on which DB you use you can manipulate the NULL to a 0 (using a case or if statement in SQL Server).
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Toby Tremayne Sent: Friday, 19 September 2003 8:58 AM To: CFAussie Mailing List Subject: [cfaussie] Re: OT: sql question again Hi Gary, yeah I've got something working at the moment with two queries, but it's bugging me now, I'm sure there must be a way to do it in one query ;) cheers, Toby Friday, September 19, 2003, 8:41:18 AM, you wrote: >> to clarify that - I need it to come back with a multipleCount of 0 >> when there are no multiple inspection sets, and still return the >> other stuff. GM> I haven't read this through in detail again..... GM> ....just do two queries - dont try and get it all returned in one GM> chunk. GM> Gary Menzel GM> Web Development Manager GM> IT Operations Brisbane -+- ABN AMRO Morgans Limited GM> Level 29, 123 Eagle Street BRISBANE QLD 4000 GM> PH: 07 333 44 828 FX: 07 3834 0828 GM> ******************************************************************** GM> ******** GM> If this communication is not intended for you and you are not an authorised GM> recipient of this email you are prohibited by law from dealing with or GM> relying on the email or any file attachments. This prohibition includes GM> reading, printing, copying, re-transmitting, disseminating, storing or in GM> any other way dealing or acting in reliance on the information. If you GM> have received this email in error, we request you contact ABN AMRO Morgans GM> Limited immediately by returning the email to [EMAIL PROTECTED] GM> and destroy the original. We will refund any reasonable costs associated GM> with notifying ABN AMRO Morgans. This email is confidential and may contain GM> privileged client information. ABN AMRO Morgans has taken reasonable steps GM> to ensure the accuracy and integrity of all its communications, including GM> electronic communications, but accepts no liability for materials GM> transmitted. Materials may also be transmitted without the knowledge of ABN GM> AMRO Morgans. ABN AMRO Morgans Limited its directors and employees do not GM> accept liability for the results of any actions taken or not on the basis GM> of the information in this report. ABN AMRO Morgans Limited and its GM> associates hold or may hold securities in the companies/trusts mentioned GM> herein. Any recommendation is made on the basis of our research of the GM> investment and may not suit the specific requirements of clients. GM> Assessments of suitability to an individual's portfolio can only be made GM> after an examination of the particular client's investments, financial GM> circumstances and requirements. GM> **************************************************************************** GM> --- GM> You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To GM> unsubscribe send a blank email to GM> [EMAIL PROTECTED] GM> MX Downunder AsiaPac DevCon - http://mxdu.com/ -------------------------------- Life is Poetry, write it in your own words -------------------------------- Toby Tremayne Cold Fusion Developer Code Poet and Zen Master of the Heavy Sleep Virtual Tours +61 416 048 090 ICQ: 13107913 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
