SQL - Outer Joins, Inline Views, and DENSE_RANK

December 7, 2009

Some time ago the following question appeared in the comp.databases.oracle.server Usenet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/a2112d7bcae62df5

 Learning SQL – will appreciate any help.
 Here is the case:

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?

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.

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.).

The original poster replied:

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.

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