On May 10, 12:02=A0pm, fergus <fergus_v...@[EMAIL PROTECTED]
> 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:
>
> =A0create table tableA (colA number not null);
> =A0create table tableB (colB number not null, colA_fk number_not null,
> colC number not null);
> =A0insert into tableA values (1);
> =A0insert into tableA values (2);
> =A0commit;
> =A0insert into tableB values (11,2,12345);
> =A0insert into tableB values (12,2,99999);
> =A0commit;
>
> select tableA.colA, tableB.colA_fk, tableB.colC from tableA left join
> tableB on tableA.colA =3D tableB.colA_fk where tableB.colC =3D 99999;
>
> =A0 --------------------------------------
> =A0 ColA =A0 ColA_FK =A0 =A0 =A0ColC
> =A0 --------------------------------------
> =A0 2 =A0 =A0 =A0 12 =A0 =A0 =A0 99999
>
> =A0I would also like to display another row:
> =A01 =A0 =A0 =A0 - =A0 =A0-
> =A0in 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=3DTABLEB.COLA_FK
AND TABLEB.COLC=3D99999;
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=3DTABLEB.COLA_FK(+)
AND TABLEB.COLC(+)=3D99999;
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.
|
