Re: SQL question on an outer join

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 10 May 2008 09:21:37 -0700 (PDT)
Message-ID: <6c42f800-f94e-4507-9330-69dc5486a36d@j33g2000pri.googlegroups.com>


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. Received on Sat May 10 2008 - 11:21:37 CDT

Original text of this message