I have these tables, tblAsset(stores the asset info), tblPMSchedules (stores the maintainence info), tblWorkOrderMaster (stores info about the work orders on assets)<BR><BR>For some weird reason, I am trying to get the asset info along with next maintainence date and work order numbers that are open for that asset (if any). <BR><BR>I could do a query which<BR><BR>SELECT&#039;s theRecordsIWant<BR>FROM tblAsset, tblPMSchedule, tblWorkOrderMaster<BR>WHERE tblAsset.asset_number = tblPMSchedule.asset_number(+) AND <BR> tblAsset.asset_number = tblWorkOrderMaster.asset_number(+)<BR><BR>P.S. (+) is for outer joins in oracle<BR><BR>but an asset could have multiple pmschedules (in which case I want to select the schedule having minimum difference between todays date and the next maintainence date) or multiple work orders (in which case I want to pick out the latest work order) <BR>I was trying to do all that in one query<BR><BR>It took me a day to get the query below to work... just wondering if anyone could suggest something better. The number of records I am dealing with is huge and performance is really the issue here<BR><BR>************************************<B R>SELECT T1.ASSET_NUMBER, ASSET_DESCRIPTION, SERIAL_NUMBER, CURRENT_LOCATION,<BR> ASSET_TYPE, RISK_PRIORITY, RISKSCORE, ASSET_COST, VENDOR_PURCHASE_COST, <BR> TO_CHAR(SYSDATE, &#039;YYYYMMDD&#039;) AS STRTODAY, <BR> TO_CHAR(T2.SERVICE_NEXTPM, &#039;YYYYMMDD&#039;) AS STRNEXTPM,<BR> T2.SERVICE_NEXTPM AS SERVICE_NEXTPM, T3.wo_number AS wo_number<BR>FROM tblAsset T1, <BR> (SELECT MIN(SERVICE_NEXTPM) AS SERVICE_NEXTPM, ASSET_NUMBER <BR> FROM TBLPMSCHEDULE GROUP BY ASSET_NUMBER) T2,<BR> (SELECT MAX(wo_number) AS wo_number, asset_number <BR> FROM tblWorkOrderMaster WHERE WO_COMPLETED IS NULL GROUP BY asset_number) T3<BR>WHERE T1.FACILITY = pFacility AND<BR> T1.TYPE_SERVICE = pService AND <BR> T1.asset_number = T2.asset_number(+) AND<BR> T1.asset_number = T3.asset_number(+) <BR>ORDER BY T1.ASSET_NUMBER<BR>******************************* *****<BR><BR>Thanks for your time