Re: SQL question on an outer join
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