Home » SQL & PL/SQL » SQL & PL/SQL » Triple Outer Join with WHERE and ORDER conditions
Triple Outer Join with WHERE and ORDER conditions [message #232877] Mon, 23 April 2007 14:44 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
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 Name:(tbl1) 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.
Re: Triple Outer Join with WHERE and ORDER conditions [message #232878 is a reply to message #232877] Mon, 23 April 2007 14:45 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
hdogg wrote on Mon, 23 April 2007 15:44

-Query3 "SELECT ActualResult from FORECAST_DATA WHERE JOBCODE LIKE '%jobcode2%' AND to_date(period,'mm/dd/rr') > '12/31/06' "



That is not a DATE, it is a character string and if that column is already a DATE, then using to_date can only make it worse.

[Updated on: Mon, 23 April 2007 14:46]

Report message to a moderator

Re: Triple Outer Join with WHERE and ORDER conditions [message #232880 is a reply to message #232878] Mon, 23 April 2007 14:49 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
Yes I understand that and it's bad programming, but that's not at all the issue i'm facing...

All three queries work, it's just a matter of getting them to join properly.

-Hyrum
Previous Topic: Table Comparison....
Next Topic: help on sql query
Goto Forum:
  


Current Time: Fri Dec 09 09:50:50 CST 2016

Total time taken to generate the page: 0.08099 seconds