Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL question on an outer join

13 views
Skip to first unread message

fergu...@yahoo.com

unread,
May 9, 2008, 11:40:53 PM5/9/08
to
Hello,
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?

Thanks in advance.
Fergus

Pat

unread,
May 10, 2008, 1:18:55 AM5/10/08
to

select tablea.cola, tableb.colb, tableb.colc from tablea left join
tableb on tablea.cola = tableb.cola_fk where tableb.colc = 9999

fergus

unread,
May 10, 2008, 12:02:53 PM5/10/08
to
> tableb on tablea.cola = tableb.cola_fk where tableb.colc = 9999- Hide quoted text -
>
> - Show quoted text -

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

Charles Hooper

unread,
May 10, 2008, 12:21:37 PM5/10/08
to

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.

fergus

unread,
May 11, 2008, 12:07:00 AM5/11/08
to
> K&M Machine-Fabricating, Inc.- Hide quoted text -

>
> - Show quoted text -


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

Charles Hooper

unread,
May 11, 2008, 9:05:01 AM5/11/08
to
> 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

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

fergus

unread,
May 11, 2008, 4:59:46 PM5/11/08
to
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -


wow!
Thank you very much - that would be my introduction to analytical
functions in oracle :-)
Awesome!

- fergus

0 new messages