Triple Outer Join with WHERE and ORDER conditions Three Tables - Job Info(tbl1) ---------- Here is the background info: JOBCODE(FK) JOBNAME -Query1 "SELECT JobName FROM JobINFO" Forecasts(tbl2) -------------- ForecastID,JobCode(PK), ForecastPoint(date)(pk), MonthFutureForecast(date), FORECASTAMOUNT* -Query2 "SELECT FORECASTAMOUNT FROM FORECASTS WHERE FORECASTPOINT LIKE '%$forecast_point%' ORDER BY JOBCODE ASC" Forecast Actuals(tbl3) ----------------- Jobcode(pk) ActualDate(date)(pk) -Query3 "SELECT ActualResult from FORECAST_DATA WHERE JOBCODE LIKE '%jobcode2%' AND to_date(period,'mm/dd/rr') > '12/31/06' " Sample Data Year:2007 Actuals(tbl3)_____ ****Forecasts(tbl2)_______________ JobCode(tbl3) Job Nametbl1) Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec 00281 ABC Company 0* 0 0 0 0 0 0 0 0 0 5,614,8 JOBCODE(from table3) is displayed no matter what even if conditions table2 or table3 don't match. JOBNAME(if exists) is joined from table 1. The actuals (from table3) are attached if they exist Forecasts(table2) Are added after the Actuals if forecasts have been made. What i would like to do: SELECT tbl3.jobcode, tbl1.JobName, Actuals.tbl3, tbl2.ForecastAmount and print it off for the month, what throws me off is doing joins on querys that already have where statements and/or order conditions with them.
Something like this. Note: Jobcode should be PK in Job Info Jobcode in forecasts is FK to Jobcode in jobinfo. If the name doesn't exist it will be NULL. Don't worry about it. select ji.jobname,fc.forecastamount,fa.actualresult from jobinfo ji, forecasts fc, forecast_actuals fa where ji .jobcode = fc.jobcode and fc.jobcode = fa.jobcode(+) and 'Blah other conditions' order by <column to be ordered by > Code (markup):