problem.. that multiple count bit works fine on it's own, but when
it's in the actual full query, I hit a snag - if there are no counts
of multiple inspections, then nothing is returned by the outer query
either, so I end up with no results - which is no good because I know
there are a bunch of single inspections in the database....
here's the query:
select i.inspection_id,
i.inspection_date,
i.propertyID,
(select min(inspection_date)
from inspection
where client_id = 234
and inspection_date > '2003-09-18 16:58:10') as nextInspection,
(select max(inspection_date)
from inspection
where client_id = 234
and inspection_date < '2003-09-18 16:58:10') as lastInspection,
COUNT(temp.Multiples) as multipleCount
from inspection i,
(SELECT COUNT(PropertyID) Multiples,
Client_ID
from inspection
GROUP BY Client_ID, PropertyID
HAVING COUNT(PropertyID) > 1) as temp
where i.client_id = 234
and i.agentId = 1
group by i.inspection_id, i.inspection_date, i.propertyID
any ideas?
--------------------------------
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/
