Two tables with 0..n relationship
TableA TableB
----------- --------------
ColA ColB
ColA_FK
Col_C
Data
TableA.ColA
====================
1
2
Table B
ColB ColA_FK Col_C
11 2 12345
12 2 99999
Resultset:
--------------------------------------
ColA ColB Col_C
--------------------------------------
1 - -
2 12 99999
In case no data exists in TableB for ColA=11, 1 from TableA shows up
in result without any data from TableB
However, in case of 2 from ColA, we want to record from TableB with
Col_C = 99999
I can do the outer join - however, how do I limit it so it picks only
the row with 9999?
Thanks in advance.
Fergus
select tablea.cola, tableb.colb, tableb.colc from tablea left join
tableb on tablea.cola = tableb.cola_fk where tableb.colc = 9999
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.
Aha - that is very cool. I had figured out the
> TABLEA.COLA=TABLEB.COLA_FK(+)
but did not know I could do this:
> AND TABLEB.COLC(+)=99999;
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=12345.
Also, working with ORACLE version 9.2.0.8.
Thank you once again,
Regards,
Fergus
Re
It looks like the DENSE_RANK analytical function, an inline view, and
an outer join are required.
First, let's introduce a little more data to make certain that we
cannot query for a specific value of COLC and return the expected
results:
INSERT INTO TABLEA VALUES (3);
INSERT INTO TABLEB VALUES (13,2,111111);
INSERT INTO TABLEB VALUES (13,3,11);
Next, we try an experiment with the DENSE_RANK function to separate
the rows by the value of COLA_FK (caused by the PARTITION BY
directive) and rank the values sorted from highest to lowest (caused
by the DESC directive):
SELECT
COLB,
COLA_FK,
COLC,
DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
FROM
TABLEB;
COLB COLA_FK COLC DR
---------- ---------- ---------- ----------
13 2 111111 1
12 2 99999 2
11 2 12345 3
13 3 11 1
We are only interested in the rows with DR = 1, so we need a way to
eliminate the unnecessary rows. If we slide the above SQL statement
into an inline view, we are able to add a WHERE clause that restricts
the results to the rows containing the highest COLC value per COLA_FK
value. We can then alias the inline view (as B), and join it to
TABLEA as before:
SELECT
TABLEA.COLA,
B.COLA_FK,
B.COLC
FROM
TABLEA,
(SELECT
COLB,
COLA_FK,
COLC,
DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
FROM
TABLEB) B
WHERE
TABLEA.COLA=B.COLA_FK(+)
AND B.DR(+)=1;
COLA COLA_FK COLC
---------- ---------- ----------
2 2 111111
3 3 11
1
wow!
Thank you very much - that would be my introduction to analytical
functions in oracle :-)
Awesome!
- fergus