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?
> 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
> > 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.
> 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.
> > 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;
> 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;
> 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.
> 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;
> > 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;
> > 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;
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;
> > > 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;
> > > 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;
> 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;
