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.
Thursday, September 18, 2003, 5:30:39 PM, you wrote:
TT> problem.. that multiple count bit works fine on it's own, but when
TT> it's in the actual full query, I hit a snag - if there are no counts
TT> of multiple inspections, then nothing is returned by the outer query
TT> either, so I end up with no results - which is no good because I know
TT> there are a bunch of single inspections in the database....
TT> here's the query:
TT> select i.inspection_id,
TT> i.inspection_date,
TT> i.propertyID,
TT> (select min(inspection_date)
TT> from inspection
TT> where client_id = 234
TT> and inspection_date > '2003-09-18 16:58:10') as nextInspection,
TT> (select max(inspection_date)
TT> from inspection
TT> where client_id = 234
TT> and inspection_date < '2003-09-18 16:58:10') as lastInspection,
TT> COUNT(temp.Multiples) as multipleCount
TT> from inspection i,
TT> (SELECT COUNT(PropertyID) Multiples,
TT> Client_ID
TT> from inspection
TT> GROUP BY Client_ID, PropertyID
TT> HAVING COUNT(PropertyID) > 1) as temp
TT> where i.client_id = 234
TT> and i.agentId = 1
TT> group by i.inspection_id, i.inspection_date, i.propertyID
TT> any ideas?
TT> --------------------------------
TT> Life is Poetry,
TT> write it in your own words
TT> --------------------------------
TT> Toby Tremayne
TT> Cold Fusion Developer
TT> Code Poet and Zen Master of the Heavy Sleep
TT> Virtual Tours
TT> +61 416 048 090
TT> ICQ: 13107913
TT> ---
TT> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
TT> To unsubscribe send a blank email to [EMAIL PROTECTED]
TT> 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/
