On May 10, 12:02*pm, fergus <fergus_v...@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.