On May 10, 11:21=A0am, Charles Hooper <hooperc2...@[EMAIL PROTECTED]
> wrote:
> 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
> =A0 TABLEA.COLA,
> =A0 TABLEB.COLA_FK,
> =A0 TABLEB.COLC
> FROM
> =A0 TABLEA
> LEFT JOIN
> =A0 TABLEB
> ON
> =A0 TABLEA.COLA=3DTABLEB.COLA_FK
> =A0 AND TABLEB.COLC=3D99999;
>
> =A0 =A0 =A0 COLA =A0 =A0COLA_FK =A0 =A0 =A0 COLC
> ---------- ---------- ----------
> =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A099999
> =A0 =A0 =A0 =A0 =A01
>
> This is the way I would commonly set up a SQL statement to meet a
> similar requirements:
> SELECT
> =A0 TABLEA.COLA,
> =A0 TABLEB.COLA_FK,
> =A0 TABLEB.COLC
> FROM
> =A0 TABLEA,
> =A0 TABLEB
> WHERE
> =A0 TABLEA.COLA=3DTABLEB.COLA_FK(+)
> =A0 AND TABLEB.COLC(+)=3D99999;
>
> =A0 =A0 =A0 COLA =A0 =A0COLA_FK =A0 =A0 =A0 COLC
> ---------- ---------- ----------
> =A0 =A0 =A0 =A0 =A01
> =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A099999
>
> Will the value of interest always be 99999, or will it be the highest
> value with a matching COLA_FK? =A0If 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=3DTABLEB.COLA_FK(+)
but did not know I could do this:
> AND TABLEB.COLC(+)=3D99999;
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=3D12345.
Also, working with ORACLE version 9.2.0.8.
Thank you once again,
Regards,
Fergus
Re
|
