Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Full Outer Join - Different results !!!!!

Re: Full Outer Join - Different results !!!!!

From: aphrikanos <aphriki_at_yahoo.com>
Date: 22 Apr 2004 03:42:15 -0700
Message-ID: <370b9881.0404220242.23e90a33@posting.google.com>


thanks for the input!
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c630e6$7c5$1_at_hercules.btinternet.com>...
> Notes in-line
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland http://www.index.is/oracleday.php
> June 2004 UK - Optimising Oracle Seminar
> July 2004 USA West Coast, Optimising Oracle Seminar
> August 2004 Charlotte NC, Optimising Oracle Seminar
> September 2004 USA East Coast, Optimising Oracle Seminar
> September2004 UK - Optimising Oracle Seminar
>
> "aphrikanos" <aphriki_at_yahoo.com> wrote in message
> news:370b9881.0404200105.cf08728_at_posting.google.com...
> > Hi all,
> > Don't know what I am doing wrong but a full outer join does not return all
> rows.
> > if I change the tables to inline views it works.
> >
> > from
> > s_d_comp_prod_qty_pvt y1
> > full outer
> > join s_d_comp_prod_qty_pvt y2
> > on y1.company_id = y2.company_id and y1.part_no = y2.part_no
>
> The result should be exactly what you see.
> The line above is your join clause, which will introduce
> rows with nulls on both sides.
>
> > where y1.company_id = 'C0031'
> > and y1.year = 2003
> > and y2.year = 2004
> >
> Then your where clause takes the join result, and
> eliminates lots of rows.
>
> Change the WHERE to an AND - which makes all three
> tests part of the join condition, and I think you will see the
> result you want.
Received on Thu Apr 22 2004 - 05:42:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US