I'll rephrase as i know i could put a grid of owners/plants date’s grouped by YYYY or YYYY/MM then have a detail grid that is the counts that i want using the master grid’s PlantKey + date-group. But that’s 2 grids. somehow I need to join those two queries.
I need a master-detail query done in a single SQL statement joining the master and the detail queries in a single result. here’s the master query (for equipment since 2010): select o.ownername, p.location, substring( r.datetested from 1 for 4 ) as RVYear, count(1) as RV_Event_Count from plants p, reliefd r join owners o on p.ownerkey = o.uniquekey where r.plantkey = p.uniquekey and r.datetested=coalesce(r.datetested, '', null, ' ' ) and r.datetested > '2010' group by o.ownername, p.location, rvyear order by rv_Event_count desc, o.ownername now, each of those rows? i want these details... this is the detail query: select o.ownername, p.location, (select count(1) from reliefd r where r.plantkey=p.uniquekey) RV_Event_count, (select count(1) from reliefd r where r.plantkey=p.uniquekey and ( (upper(substring( r.manufacturer from 1 for 4))='CONS') or (upper(substring( r.manufacturer from 1 for 4))='DRES') )) RV_Cons_count, (select count(1) from cv c where c.plantkey=p.uniquekey and c.mostrecent='T') cV_event_count, (select count(1) from cv c where c.plantkey=p.uniquekey and upper(substring( c.manufacturer from 1 for 4))='MASO' ) CV_mason_count from owners o, plants p where p.ownerkey = o.uniquekey ------ in here i’d need the row i’m on from the master query ---- and (detail)p.location = :(master.p.location) and (detail).substring( r.datetested from 1 for 4 ) = :(master.p.RVYear) order by rv_event_count desc, o.ownername
