SEO

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

On May 9, 8:40 pm, fergus_v...@yahoo.com wrote:
> 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

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

On May 10, 12:18*am, Pat <pat.ca...@service-now.com> wrote:
> On May 9, 8:40 pm, fergus_v...@yahoo.com wrote:
>
>
>
>
>
> > 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
>
> select tablea.cola, tableb.colb, tableb.colc from tablea left join
> 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

On May 10, 12:02*pm, fergus <fergus_v...@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.

On May 10, 11:21*am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On May 10, 12:02*pm, fergus <fergus_v...@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.- 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

On May 11, 12:07*am, fergus <fergus_v...@yahoo.com> wrote:
> On May 10, 11:21*am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > 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

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

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On May 11, 8:05*am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On May 11, 12:07*am, fergus <fergus_v...@yahoo.com> wrote:
>
>
>
>
>
> > On May 10, 11:21*am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > > 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
>
> 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
>
> Charles Hooper
> IT Manager/Oracle DBA
> 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