Re: SQL question on an outer join

On May 10, 11:21 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On May 10, 12:02 pm, fergus <fergus_v..._at_yahoo.com> wrote:
>
>
>
>
>
> > First of all, thanks for your input.
> > However, the query you posted does not seem to be producing the
> > correct result set per my question.
>
> > Here is the test case:
>
> >  create table tableA (colA number not null);
> >  create table tableB (colB number not null, colA_fk number_not null,
> > colC number not null);
> >  insert into tableA values (1);
> >  insert into tableA values (2);
> >  commit;
> >  insert into tableB values (11,2,12345);
> >  insert into tableB values (12,2,99999);
> >  commit;
>
> > select tableA.colA, tableB.colA_fk, tableB.colC from tableA left join
> > tableB on tableA.colA = tableB.colA_fk where tableB.colC = 99999;
>
> >   --------------------------------------
> >   ColA   ColA_FK      ColC
> >   --------------------------------------
> >   2       12       99999
>
> >  I would also like to display another row:
> >  1       -    -
> >  in there.
>
> > Thanks
> > -- Fergus
>
> Thanks for posting the DDL and DML for the setup.
>
> Using the suggestion offered by Pat, with a small modification:
> SELECT
>   TABLEA.COLA,
>   TABLEB.COLA_FK,
>   TABLEB.COLC
> FROM
>   TABLEA
> LEFT JOIN
>   TABLEB
> ON
>   TABLEA.COLA=TABLEB.COLA_FK
>   AND TABLEB.COLC=99999;
>
>       COLA    COLA_FK       COLC
> ---------- ---------- ----------
>          2          2      99999
>          1
>
> This is the way I would commonly set up a SQL statement to meet a
> similar requirements:
> SELECT
>   TABLEA.COLA,
>   TABLEB.COLA_FK,
>   TABLEB.COLC
> FROM
>   TABLEA,
>   TABLEB
> WHERE
>   TABLEA.COLA=TABLEB.COLA_FK(+)
>   AND TABLEB.COLC(+)=99999;
>
>       COLA    COLA_FK       COLC
> ---------- ---------- ----------
>          1
>          2          2      99999
>
> Will the value of interest always be 99999, or will it be the highest
> value with a matching COLA_FK?  If you are looking for the highest
> value, please supply the four digit version of Oracle that you are
> using (10.2.0.2, 11.1.0.6, 8.1.7.3, etc.).
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Aha - that is very cool. I had figured out the   > TABLEA.COLA=TABLEB.COLA_FK(+) but did not know I could do this:

 > AND TABLEB.COLC(+)=99999; As a matter of fact you are right - how did you guess - in my cases, the interest of value would be max of whatever  is in TABLEB.COLC - if the row with 99999 does not exist, then the sql returns the fow with data in COLC=12345.  Also, working with ORACLE version 9.2.0.8.

Thank you once again,

Regards,
Fergus

Re Received on Sat May 10 2008 - 23:07:00 CDT