Home » SQL & PL/SQL » SQL & PL/SQL » LEFT OUTER JOIN ??? -quick question
LEFT OUTER JOIN ??? -quick question [message #232437] Fri, 20 April 2007 09:17 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
I am using oracle with php. I am trying to accomplish a left outer join.

Here are the two queries.

Query 1, contains all the data on the left the will show up with data on the right.

Query 1 =
((SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )))

Query 2 - is appended to query 1, while query still displays results when there are no matches on query 2.

Query 2 =
"SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ORDER BY JOBCODE ASC");
Re: LEFT OUTER JOIN ??? -quick question [message #232453 is a reply to message #232437] Fri, 20 April 2007 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from (query1) q1, (query2) q2 where q2.jobcode (+) = q1.jobcode

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232454 is a reply to message #232453] Fri, 20 April 2007 10:30 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
i see that the outer join also uses a WHERE statement, can use a where statement in QUERY 1 to limit the results of QUERY 1?
Re: LEFT OUTER JOIN ??? -quick question [message #232456 is a reply to message #232454] Fri, 20 April 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An outer join never limit something, it just adds some data if they are available.
Nevertheless, you can merge the 2 queries to get only one.
Just try it and post what you tried.

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232457 is a reply to message #232437] Fri, 20 April 2007 10:57 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Before I go to the union....


here is my situation...

$left_outer_join_monthly_data = oci_parse($c, "select * from (SELECT JOB_CODE AS JOBCODE_A FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' ) ORDER BY JOBCODE_A ASC) q1, (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ORDER BY JOBCODE ASC) q2 where q2.jobcode (+) = q1.jobcode_A");




oci_execute($left_outer_join_monthly_data);
$i = 1;
while($res=oci_fetch_array($left_outer_join_monthly_data))
{


$jobcode = $res['JOBCODE_A'];
echo '<tr><td>'.$jobcode.'</td>';

$forecast = 0;
if(isset($res['FORECAST'])){
$forecast = $res['FORECAST'];
}
$forecast = number_format($forecast,0);
echo '<td> $'.$forecast.'</td>';

}

The query is just fine, as far as doing the union and not throwing errors...

However, when I display the results... JOBCODES are on the left, and the Jan thru feb goes across the top. So the forecasts go on each row.

So the problem is that every JOBCODE FROM QUERY 1 is listed, but it doesn't add any of query 2, i am certain that i am just not outputing it right... what is wrong?
All the best,
Hyrum S

Re: LEFT OUTER JOIN ??? -quick question [message #232458 is a reply to message #232437] Fri, 20 April 2007 10:58 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Here are the exact results:

Year: 20--
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
Job Code
02572 $0
03678 $0
03692 $0
04724 $0
04724A $0
04724B $0
04725 $0
05011 $0
05012 $0
05014 $0
Re: LEFT OUTER JOIN ??? -quick question [message #232459 is a reply to message #232457] Fri, 20 April 2007 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK misunderstood your question.
Actually you don't want an "outer join" but a "union" of both queries, doesn't it?

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232461 is a reply to message #232459] Fri, 20 April 2007 11:11 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
hmmm....

If I were to UNION both queries wouldn't that only display results that had both results in query 1 and query 2? I always want results from query 1, but when they exist i want forecasts from query 2 to go with their respected job.

Regards,

Hyrum
Re: LEFT OUTER JOIN ??? -quick question [message #232468 is a reply to message #232459] Fri, 20 April 2007 11:56 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
So the left outer join is not the way to go?
Re: LEFT OUTER JOIN ??? -quick question [message #232483 is a reply to message #232468] Fri, 20 April 2007 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FULL outer join seems to be your solution.
select nvl(a.jobcode,b.jobcode), b.forecast
from (query1) a full outer join (query2) b on (a.jobcode=b.jobcode)

Or the like I didn't check the syntax.

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232484 is a reply to message #232483] Fri, 20 April 2007 12:48 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
yeah, it yells at me, there's a bug in the syntax i think..

-Hyrum
Re: LEFT OUTER JOIN ??? -quick question [message #232487 is a reply to message #232484] Fri, 20 April 2007 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't take into account "query1" and "query2", syntax is correct in what I posted (unless you use a version that does not support "full outer join").

select a.id, b.name 
from (select * from t) a full outer join (select * from t) b on (a.id=b.id);


Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232488 is a reply to message #232484] Fri, 20 April 2007 13:02 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
here is what i put...

$left_outer_join_monthly_data = oci_parse($c, "select nv1(q1.jobcode, q2.jobcode), b.forecast from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 FULL OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 on q2.jobcode = q1.jobcode");
Re: LEFT OUTER JOIN ??? -quick question [message #232489 is a reply to message #232488] Fri, 20 April 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the error is?
And your Oracle version is?

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232491 is a reply to message #232437] Fri, 20 April 2007 13:13 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Version 10G.

Here is the error message.

Warning: oci_execute() [function.oci-execute]: ORA-00904: "PCT_COMP_CODE": invalid identifier in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 120

Warning: oci_fetch_array() [function.oci-fetch-array]: ¨ in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 122

Here is my code:

$left_outer_join_monthly_data = oci_parse($c, "select q1.jobcode, q2.jobcode from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 FULL OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 on q1.jobcode = q2.jobcode");

*Note there was no error with the left outer join.. so there error is from the FULL OUTER JOIN.
Re: LEFT OUTER JOIN ??? -quick question [message #232493 is a reply to message #232491] Fri, 20 April 2007 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You say that if you just change "full" to "left" there is no more error?
"full outer join" is union of "right" and "left" outer join, so split the query.

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232503 is a reply to message #232493] Fri, 20 April 2007 14:41 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Yeah, i just changed it to left and the error stopped -

Here are some of the results I get:

ear: 20--
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
Job Code
02572
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03692
04724
04724A
04724B
04725
05011
05012
05014
05023
05023A4
05023A42
05023C25
05023C26
05023C27
05023C28
05023C29
05023C30
05023C31
05023C32
05023C33

As you can see Forecast_Input(query 2) has 24 forecasts, hence JOBCODE 03678 appears 24 times- and no forecasts appear horizantly.

Would splitting the query solve that?

By splitting it would I do something like do a right AND left outer join or something?

Regards,

Hyrum
Re: LEFT OUTER JOIN ??? -quick question [message #232504 is a reply to message #232503] Fri, 20 April 2007 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232505 is a reply to message #232504] Fri, 20 April 2007 14:57 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
How do I split a query?

Like this ???

$left_outer_join_monthly_data = oci_parse($c, "select q1.jobcode, q2.jobcode from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 LEFT OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 on q1.jobcode = q2.jobcode") q3 RIGHT OUTER JOIN q3 ON q3.jobcode = q2jobcode;

Is that getting closer?

-Hyrum
Re: LEFT OUTER JOIN ??? -quick question [message #232506 is a reply to message #232505] Fri, 20 April 2007 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just "q1 left outer join q2 union q1 right outer join q2".

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232510 is a reply to message #232437] Fri, 20 April 2007 15:16 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
I am getting way close, I am just off on the syntax...

I put $left_outer_join_monthly_data = oci_parse($c, "select * from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 LEFT OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 (on q2.jobcode = q1.jobcode) UNION q1 (on q2.jobcode = q1.jobcode) right outer join q2 (on q2.jobcode = q1.jobcode)");

Do I only need one ON condition? Or are they in the wrong spot?

Thanks so much -

-Hyrum



Here is the error...


Warning: oci_execute() [function.oci-execute]: ORA-00905: missing keyword in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 121

Warning: oci_fetch_array() [function.oci-fetch-array]: ¨ in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 123
Re: LEFT OUTER JOIN ??? -quick question [message #232512 is a reply to message #232510] Fri, 20 April 2007 15:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you serious or are you kidding?

1/ Take the query with full outer join
2/ Copy and paste it to make it 2
3/ Change "full" to "left" in first query
4/ Change "full" to "right" in second query
5/ Put "union" between the 2 queries

Is this clear?

Regards
Michel
Re: LEFT OUTER JOIN ??? -quick question [message #232513 is a reply to message #232437] Fri, 20 April 2007 15:33 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Dude, I am a flipping retard!! Thanks a bunch that worked.

This is correct!


Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
Job Code
02572
03678
03692
04724
04724A
04724B
04725
05011
05012
05014
05023
05023A4
05023A42
05023C25
05023C26
05023C27
05023C28
05023C29
05023C30
05023C31
05023C32
05023C33
05023C34
05023C35
05023C36
05023C37
05023C38
05023C39
05023C43


Best Regards --

Thanks a bunch again.

-Hyrum

Re: LEFT OUTER JOIN ??? -quick question [message #232515 is a reply to message #232512] Fri, 20 April 2007 15:45 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
One other issue....

I can return jobcode great as you see by the left...

but when i return the FORECAST column $res['FORECAST'] I get a big fat "undefined variable" error... it seems like the query only returns the job codes.
Re: LEFT OUTER JOIN ??? -quick question [message #232516 is a reply to message #232437] Fri, 20 April 2007 15:48 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
actually, i think i got it, i found out i didn't added to every sub query... it does throw errors on the records that have no forecasts... would it be better to do an isset(forecast) on the results and populate an array?
Previous Topic: Materialized View
Next Topic: Index on column with 90% rows Null
Goto Forum:
  


Current Time: Thu Dec 08 16:09:17 CST 2016

Total time taken to generate the page: 0.12624 seconds