sql help - LEFT JOIN, want to include rows that do not have a match
Hi EE,
This is a project for work. I work for a school.
I want to pull students from ACADEMIC_STUDY, and ADDRESS_CURRENT tables.
The ADDRESS_CURRENT table can have many different rows for ONE student, because we carry many different addresses.
MA - mailing address
BU - business address (for staff members)
FA - for financial aid to be sent to
So since I am pulling students, i want MA information. If the student DID NOT provide mailing info. They would not have a MA row, but i still want to include them.
Sample data
ACADEMIC_STUDY
1111111 Washington, George
2222222 Lincoln, Abraham
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
results set
1111111 Washington, George MA
2222222 Lincoln, Abraham null
I coded IN ('MA', NULL), but I think Oracle is handling this as an EQUI JOIN and i am not getting the unmatched records
Do I have to code the WHERE CLAUSE in a special way like with a + sign?
tx for your help, sandra
======
select ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT.ADDRESS_TY
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUD
,ODSMGR.ADDRESS_CURRENT ADDRESS_CURRENT
where ACADEMIC_STUDY.ACADEMIC_PE
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program)
and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STA
AND ADDRESS_CURRENT.ADDRESS_TY
order by
ACADEMIC_STUDY.ACADEMIC_PE
,ACADEMIC_STUDY.NAME
This is a project for work. I work for a school.
I want to pull students from ACADEMIC_STUDY, and ADDRESS_CURRENT tables.
The ADDRESS_CURRENT table can have many different rows for ONE student, because we carry many different addresses.
MA - mailing address
BU - business address (for staff members)
FA - for financial aid to be sent to
So since I am pulling students, i want MA information. If the student DID NOT provide mailing info. They would not have a MA row, but i still want to include them.
Sample data
ACADEMIC_STUDY
1111111 Washington, George
2222222 Lincoln, Abraham
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
results set
1111111 Washington, George MA
2222222 Lincoln, Abraham null
I coded IN ('MA', NULL), but I think Oracle is handling this as an EQUI JOIN and i am not getting the unmatched records
Do I have to code the WHERE CLAUSE in a special way like with a + sign?
tx for your help, sandra
======
select ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT.ADDRESS_TY
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUD
,ODSMGR.ADDRESS_CURRENT ADDRESS_CURRENT
where ACADEMIC_STUDY.ACADEMIC_PE
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program)
and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STA
AND ADDRESS_CURRENT.ADDRESS_TY
order by
ACADEMIC_STUDY.ACADEMIC_PE
,ACADEMIC_STUDY.NAME
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
When you use IN, you're telling SQL to take a value and compare it against every value or set of values in a list using =. If any NULL values exist, a row will not be returned--even if both values are NULL.
Source: http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/#.
Just remove NULL from IN
Source: http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/#.
Just remove NULL from IN
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Hi Gentlemen,
thx for writing....
I am coming from a Microsoft Access background and I am used to coding LEFT OUTER JOIN.
Truthfully, i never really thought about that the LEFT OUT JOIN implicitly works hand in hand with the WHERE CLAUSE that it would automatically return the NULL rows.
You see, at work, we have a tool at work to create Queries, that is called Oracle Discoverer that does not use ANSI.
and work purchased a new tool to create reports. To save time, i wanted to copy the SQL from Discoverer to New tool. Also by using similar SQL, I could have both tools opened side by side to test that my new reports in the new tool match Discoverer (sorry am wary of mentioning new tool's name bec i am not a fan..... :-) )
Now i could be wrong, but must each table in ANSI be explicitly connected with JOIN statements, and that in traditional Oracle you could join whatever you want.
FROM TABLE A
INNER JOIN TABLE A TO TABLE B
ON FIELDA = FIELDB
now if i want to work with table c join to table d, and they have no relationship to the above tables how to code
yet in traditional Oracle you can join whatever you want in the WHERE clause
believe me I am an ANSI join fan, but the above was an impression i got from reading Oracle Discoverer sql.
So I was really looking for in this post, this answer, yet i have to go test it....
where t1.col1=t2.col1(+)
While i am more comfortable with ANSI joins, here my mentor in the IT dept uses traditional joins
and i thought it would be easier if i have questions, or am on vacation or leave, if i try to adapt to her style....
tx, sandra
thx for writing....
I am coming from a Microsoft Access background and I am used to coding LEFT OUTER JOIN.
Truthfully, i never really thought about that the LEFT OUT JOIN implicitly works hand in hand with the WHERE CLAUSE that it would automatically return the NULL rows.
You see, at work, we have a tool at work to create Queries, that is called Oracle Discoverer that does not use ANSI.
and work purchased a new tool to create reports. To save time, i wanted to copy the SQL from Discoverer to New tool. Also by using similar SQL, I could have both tools opened side by side to test that my new reports in the new tool match Discoverer (sorry am wary of mentioning new tool's name bec i am not a fan..... :-) )
Now i could be wrong, but must each table in ANSI be explicitly connected with JOIN statements, and that in traditional Oracle you could join whatever you want.
FROM TABLE A
INNER JOIN TABLE A TO TABLE B
ON FIELDA = FIELDB
now if i want to work with table c join to table d, and they have no relationship to the above tables how to code
yet in traditional Oracle you can join whatever you want in the WHERE clause
believe me I am an ANSI join fan, but the above was an impression i got from reading Oracle Discoverer sql.
So I was really looking for in this post, this answer, yet i have to go test it....
where t1.col1=t2.col1(+)
While i am more comfortable with ANSI joins, here my mentor in the IT dept uses traditional joins
and i thought it would be easier if i have questions, or am on vacation or leave, if i try to adapt to her style....
tx, sandra
Sorry, i made a mistake:
i am working with the + like this:
and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
i just wanted to pull one kind of row (the rows with the MA) and the students who do not have an MA (yet these students could have other kinds of address records like BU, FA etc)
and using traditional Oracle i do not know how to do that
Yet how did traditional Oracle programmer do this in the past before adopting ANSI standards?
tx... sandra
i am working with the + like this:
and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
i just wanted to pull one kind of row (the rows with the MA) and the students who do not have an MA (yet these students could have other kinds of address records like BU, FA etc)
and using traditional Oracle i do not know how to do that
Yet how did traditional Oracle programmer do this in the past before adopting ANSI standards?
tx... sandra
Hi Everyone,
I changed it to ANSI left OUTER JOIN..... still did not get the students who do not have MA, only got the students that
have MA.
@SlightWV - should i do the technique you showed me in a post last week using ROW NUM to see if the MA record exists? sql from that project is below
select ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT.ADDRESS_TY
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUD
LEFT OUTER JOIN ODSMGR.ADDRESS_CURRENT ADDRESS_CURRENT
ON ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
where ACADEMIC_STUDY.ACADEMIC_PE
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program)
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STA
AND ADDRESS_CURRENT.ADDRESS_TY
order by
ACADEMIC_STUDY.ACADEMIC_PE
,ACADEMIC_STUDY.NAME
tx, sandra
I changed it to ANSI left OUTER JOIN..... still did not get the students who do not have MA, only got the students that
have MA.
@SlightWV - should i do the technique you showed me in a post last week using ROW NUM to see if the MA record exists? sql from that project is below
select ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT.ADDRESS_TY
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUD
LEFT OUTER JOIN ODSMGR.ADDRESS_CURRENT ADDRESS_CURRENT
ON ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
where ACADEMIC_STUDY.ACADEMIC_PE
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program)
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STA
AND ADDRESS_CURRENT.ADDRESS_TY
order by
ACADEMIC_STUDY.ACADEMIC_PE
,ACADEMIC_STUDY.NAME
tx, sandra
The sql that @SlightWV helped me with last week
SELECT
ACADEMIC_STUDY.ACADEMIC_YE
, ACADEMIC_STUDY.COLLEGE AS AS_COLLEGE
, ODSMGR.DMC_MAX_TERM_FOR_ST
, ACADEMIC_STUDY.STUDENT_CLA
, ACADEMIC_STUDY.ID AS AS_ID
, ACADEMIC_STUDY.name AS AS_NAME
FROM
ACADEMIC_STUDY ACADEMIC_STUDY
, (SELECT DISTINCT STUDENT_COURSEB.ACADEMIC_Y
, STUDENT_COURSEB.ID
, STUDENT_COURSEB.name
FROM
(
select STUDENT_COURSEA.academic_y
, STUDENT_COURSEA.COURSE_IDE
, STUDENT_COURSEA.ID
, STUDENT_COURSEA.name
, STUDENT_COURSEA.FINAL_GRAD
, STUDENT_COURSEA.RN
from (
select
STUDENT_COURSE.academic_ye
, STUDENT_COURSE.ID
, STUDENT_COURSE.name
, STUDENT_COURSE.COURSE_IDEN
, STUDENT_COURSE.FINAL_GRADE
, row_number() over(partition by STUDENT_COURSE.id
, STUDENT_COURSE.course_iden
order by STUDENT_COURSE.id
, STUDENT_COURSE.course_iden
, STUDENT_COURSE.academic_ye
, STUDENT_COURSE.FINAL_GRADE
AS rn
from STUDENT_COURSE
where (STUDENT_COURSE.COLLEGE = 'MD'
AND
STUDENT_COURSE.ACADEMIC_YE
AND
( SUBSTR(STUDENT_COURSE.COUR
OR
STUDENT_COURSE.COURSE_IDEN
--ORDER BY STUDENT_COURSE.COURSE_IDEN
-- ,STUDENT_COURSE.NAME
-- ,STUDENT_COURSE.ACADEMIC_Y
) STUDENT_COURSEA
where rn > 1
AND STUDENT_COURSEA.ACADEMIC_Y
AND FINAL_GRADE <> 'Y'
--ORDER BY STUDENT_COURSEA.COURSE_IDE
-- ,STUDENT_COURSEA.NAME
) STUDENT_COURSEB
) STUDENT_COURSEC
WHERE
-- ACADEMIC_STUDY.ID = '200714348'
-- AND
ACADEMIC_STUDY.COLLEGE = 'MD'
AND
ACADEMIC_STUDY.ACADEMIC_YE
AND
ACADEMIC_STUDY.ACADEMIC_PE
-- JOINING: STUDENT_COURSEC TO ACADEMIC_STUDY
AND
(STUDENT_COURSEC.ACADEMIC_
AND
STUDENT_COURSEC.ID = ACADEMIC_STUDY.ID (+))
and
:BT_MC_RepeatedCourse_Deta
ORDER BY
ACADEMIC_STUDY.STUDENT_CLA
, STUDENT_COURSEC.NAME
SELECT
ACADEMIC_STUDY.ACADEMIC_YE
, ACADEMIC_STUDY.COLLEGE AS AS_COLLEGE
, ODSMGR.DMC_MAX_TERM_FOR_ST
, ACADEMIC_STUDY.STUDENT_CLA
, ACADEMIC_STUDY.ID AS AS_ID
, ACADEMIC_STUDY.name AS AS_NAME
FROM
ACADEMIC_STUDY ACADEMIC_STUDY
, (SELECT DISTINCT STUDENT_COURSEB.ACADEMIC_Y
, STUDENT_COURSEB.ID
, STUDENT_COURSEB.name
FROM
(
select STUDENT_COURSEA.academic_y
, STUDENT_COURSEA.COURSE_IDE
, STUDENT_COURSEA.ID
, STUDENT_COURSEA.name
, STUDENT_COURSEA.FINAL_GRAD
, STUDENT_COURSEA.RN
from (
select
STUDENT_COURSE.academic_ye
, STUDENT_COURSE.ID
, STUDENT_COURSE.name
, STUDENT_COURSE.COURSE_IDEN
, STUDENT_COURSE.FINAL_GRADE
, row_number() over(partition by STUDENT_COURSE.id
, STUDENT_COURSE.course_iden
order by STUDENT_COURSE.id
, STUDENT_COURSE.course_iden
, STUDENT_COURSE.academic_ye
, STUDENT_COURSE.FINAL_GRADE
AS rn
from STUDENT_COURSE
where (STUDENT_COURSE.COLLEGE = 'MD'
AND
STUDENT_COURSE.ACADEMIC_YE
AND
( SUBSTR(STUDENT_COURSE.COUR
OR
STUDENT_COURSE.COURSE_IDEN
--ORDER BY STUDENT_COURSE.COURSE_IDEN
-- ,STUDENT_COURSE.NAME
-- ,STUDENT_COURSE.ACADEMIC_Y
) STUDENT_COURSEA
where rn > 1
AND STUDENT_COURSEA.ACADEMIC_Y
AND FINAL_GRADE <> 'Y'
--ORDER BY STUDENT_COURSEA.COURSE_IDE
-- ,STUDENT_COURSEA.NAME
) STUDENT_COURSEB
) STUDENT_COURSEC
WHERE
-- ACADEMIC_STUDY.ID = '200714348'
-- AND
ACADEMIC_STUDY.COLLEGE = 'MD'
AND
ACADEMIC_STUDY.ACADEMIC_YE
AND
ACADEMIC_STUDY.ACADEMIC_PE
-- JOINING: STUDENT_COURSEC TO ACADEMIC_STUDY
AND
(STUDENT_COURSEC.ACADEMIC_
AND
STUDENT_COURSEC.ID = ACADEMIC_STUDY.ID (+))
and
:BT_MC_RepeatedCourse_Deta
ORDER BY
ACADEMIC_STUDY.STUDENT_CLA
, STUDENT_COURSEC.NAME
Just like last week: How about some small test cases instead of EVERYTHING.
I still don't understand the complete SQL you keep posting and I really doubt I ever will.
If you can post a small test case and expected results then we can post working SQL based from that. You can then take what we post and apply it back to your larger select.
I still don't understand the complete SQL you keep posting and I really doubt I ever will.
If you can post a small test case and expected results then we can post working SQL based from that. You can then take what we post and apply it back to your larger select.
Hi SlightWV,
is this ok? tx, sandra
Sample data
ACADEMIC_STUDY
1111111 Washington, George AS
2222222 Lincoln, Abraham LA
fields: id, name, student status
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
fields: id, name, address_type
results set (join of the tables)
1111111 Washington, George MA AS
2222222 Lincoln, Abraham null LA
is this ok? tx, sandra
Sample data
ACADEMIC_STUDY
1111111 Washington, George AS
2222222 Lincoln, Abraham LA
fields: id, name, student status
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
fields: id, name, address_type
results set (join of the tables)
1111111 Washington, George MA AS
2222222 Lincoln, Abraham null LA
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Hi @PortletPaul,
thx for writing....
yes, work purchased a different reporting tool because Oracle will no longer support the Discoverer BI tool.
i wanted to compare results using the == same == SQL with both tools open.
if i would have the same results in new tool, i know my new tool's query is fine.
i do have access sql plus, but have no experience. i pasted my sql there, it ran, the rows just zipped on the screen. can i export from sql plus to Excel?
Hi @SlightWV,
I understand your question, now:
Asking me that both students are existing in both tables so how could it be an OUTER JOIN
since the join is on Student Id.
I mistakenly thought the WHERE ADDRESS_TYPE = 'MA' would be control/be part of the OUTER JOIN
that even though YES matching on Id for both students, yet, bec the Lincoln, Abraham does not have a MA record, it would be like there was no match.
So maybe I should pull the MAs into a virtual table, and if I join ACADEMIC_STUDY to virtual table, Lincoln, Abe will not have a match.
Will go work on it now.... thx, sandra
thx for writing....
yes, work purchased a different reporting tool because Oracle will no longer support the Discoverer BI tool.
i wanted to compare results using the == same == SQL with both tools open.
if i would have the same results in new tool, i know my new tool's query is fine.
i do have access sql plus, but have no experience. i pasted my sql there, it ran, the rows just zipped on the screen. can i export from sql plus to Excel?
Hi @SlightWV,
I understand your question, now:
Asking me that both students are existing in both tables so how could it be an OUTER JOIN
since the join is on Student Id.
I mistakenly thought the WHERE ADDRESS_TYPE = 'MA' would be control/be part of the OUTER JOIN
that even though YES matching on Id for both students, yet, bec the Lincoln, Abraham does not have a MA record, it would be like there was no match.
So maybe I should pull the MAs into a virtual table, and if I join ACADEMIC_STUDY to virtual table, Lincoln, Abe will not have a match.
Will go work on it now.... thx, sandra
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Hi Gentlemen,
So this SQL works and gives me the results needed.... just wish it would run a little bit faster
especially, that this is just a piece of a large SQL that used more tables and more joins
From this post , absorbed concepts about:
= Oracle world is moving toward ANSI style of coding table joins
= about testing for a value on WHERE CLAUSE with OUTER JOIN gives EQUI join results
so that if want the unmatched rows, to create a virtual table and join against that
thx everyone, sandra
So this SQL works and gives me the results needed.... just wish it would run a little bit faster
especially, that this is just a piece of a large SQL that used more tables and more joins
From this post , absorbed concepts about:
= Oracle world is moving toward ANSI style of coding table joins
= about testing for a value on WHERE CLAUSE with OUTER JOIN gives EQUI join results
so that if want the unmatched rows, to create a virtual table and join against that
thx everyone, sandra
select ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT_2.STREET_L
ADDRESS_CURRENT_2.STREET_L
ADDRESS_CURRENT_2.ADDRESS_
ADDRESS_CURRENT_2.CITY,
ADDRESS_CURRENT_2.STATE_PR
ADDRESS_CURRENT_2.NATION_D
ADDRESS_CURRENT_2.PREFERRE
ADDRESS_CURRENT_2.COUNTY_D
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUD
LEFT OUTER JOIN
( SELECT
ADDRESS_CURRENT.ENTITY_UID
ADDRESS_CURRENT.STREET_LIN
ADDRESS_CURRENT.STREET_LIN
ADDRESS_CURRENT.ADDRESS_TY
ADDRESS_CURRENT.CITY,
ADDRESS_CURRENT.STATE_PROV
ADDRESS_CURRENT.NATION_DES
ADDRESS_CURRENT.PREFERRED_
ADDRESS_CURRENT.COUNTY_DES
FROM
ADDRESS_CURRENT
WHERE
ADDRESS_TYPE = 'MA'
) ADDRESS_CURRENT_2
ON ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT_2.ENTITY_U
where ACADEMIC_STUDY.ACADEMIC_PE
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program)
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STA
order by
ACADEMIC_STUDY.ACADEMIC_PE
,ACADEMIC_STUDY.NAME
ACADEMIC_STUDY.ACADEMIC_YE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ACADEMIC_PE
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT_2.STREET_L
ADDRESS_CURRENT_2.STREET_L
ADDRESS_CURRENT_2.ADDRESS_
ADDRESS_CURRENT_2.CITY,
ADDRESS_CURRENT_2.STATE_PR
ADDRESS_CURRENT_2.NATION_D
ADDRESS_CURRENT_2.PREFERRE
ADDRESS_CURRENT_2.COUNTY_D
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUD
LEFT OUTER JOIN
( SELECT
ADDRESS_CURRENT.ENTITY_UID
ADDRESS_CURRENT.STREET_LIN
ADDRESS_CURRENT.STREET_LIN
ADDRESS_CURRENT.ADDRESS_TY
ADDRESS_CURRENT.CITY,
ADDRESS_CURRENT.STATE_PROV
ADDRESS_CURRENT.NATION_DES
ADDRESS_CURRENT.PREFERRED_
ADDRESS_CURRENT.COUNTY_DES
FROM
ADDRESS_CURRENT
WHERE
ADDRESS_TYPE = 'MA'
) ADDRESS_CURRENT_2
ON ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT_2.ENTITY_U
where ACADEMIC_STUDY.ACADEMIC_PE
ODSMGR.DMC_MAX_TERM_FOR_ST
'201330',
'N',
ACADEMIC_STUDY.Program)
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STA
order by
ACADEMIC_STUDY.ACADEMIC_PE
,ACADEMIC_STUDY.NAME
>> so that if want the unmatched rows, to create a virtual table and join against that
Not sure if this is necessary or not. Again, I do not understand your table structures or what you are trying to achieve. I've never received a simplified test case that I've been asking for.
Not sure if this is necessary or not. Again, I do not understand your table structures or what you are trying to achieve. I've never received a simplified test case that I've been asking for.
Hi SlightWV,
guess i do not understand the definition of "test case".... thought that means "test data" which typed above that am pasting below to make it easier:
Sample data
ACADEMIC_STUDY
1111111 Washington, George AS
2222222 Lincoln, Abraham LA
fields: id, name, student status
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
fields: id, name, address_type
results set (join of the tables) that SQL should provide
1111111 Washington, George MA AS
2222222 Lincoln, Abraham null LA
fields: id, name, address_type, student_status
Even thought Lincoln, Abraham is indeed in the ADDRESS_CURRENT table, he does not have a MA record he has a BU record, so it's really a mismatch for business purposes, like he does not exist in the ADDRESS_CURRENT table.
So what i learned is to create a virtual sql table just containing the MA records, and join to that, so that Lincoln, Abraham shows but with null in the address_type field, bec he truly is not in the MA subset.
I suppose there are many ways using SQL to create the subset. I just did the virtual table idea to join to.
I wonder which is the fastest technique to create a subset.... for now, I learned key concepts through this post, so i am happy....
I am going to try the sql plus tips that you provided...
will write back soon... thx, sandra
guess i do not understand the definition of "test case".... thought that means "test data" which typed above that am pasting below to make it easier:
Sample data
ACADEMIC_STUDY
1111111 Washington, George AS
2222222 Lincoln, Abraham LA
fields: id, name, student status
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
fields: id, name, address_type
results set (join of the tables) that SQL should provide
1111111 Washington, George MA AS
2222222 Lincoln, Abraham null LA
fields: id, name, address_type, student_status
Even thought Lincoln, Abraham is indeed in the ADDRESS_CURRENT table, he does not have a MA record he has a BU record, so it's really a mismatch for business purposes, like he does not exist in the ADDRESS_CURRENT table.
So what i learned is to create a virtual sql table just containing the MA records, and join to that, so that Lincoln, Abraham shows but with null in the address_type field, bec he truly is not in the MA subset.
I suppose there are many ways using SQL to create the subset. I just did the virtual table idea to join to.
I wonder which is the fastest technique to create a subset.... for now, I learned key concepts through this post, so i am happy....
I am going to try the sql plus tips that you provided...
will write back soon... thx, sandra
>>which typed above that am pasting below to make it easier:
Sorry I missed some stuff during this thread.
Now that I've put it all together, I don't think you need the inline view (what you call a virtual table).
For my test case based on your data posted above, see if this works better for you:
Sorry I missed some stuff during this thread.
Now that I've put it all together, I don't think you need the inline view (what you call a virtual table).
For my test case based on your data posted above, see if this works better for you:
--drop TABLE ACADEMIC_STUDY purge;
create TABLE ACADEMIC_STUDY (
id number,
name varchar2(30),
status char(2)
);
--drop TABLE ADDRESS_CURRENT purge;
create TABLE ADDRESS_CURRENT (
id number,
name varchar2(30),
address_type char(2)
);
--ACADEMIC_STUDY
insert into academic_study values(1111111,'Washington, George','AS');
insert into academic_study values(2222222,'Lincoln, Abraham','LA');
--ADDRESS_CURRENT
insert into address_current values(1111111,'Washington, George','MA');
insert into address_current values(2222222,'Lincoln, Abraham','BU');
commit;
select s.id, s.name, c.address_type, s.status
from academic_study s
left outer join address_current c
on s.id=c.id and
s.name=c.name and
c.address_type='MA'
/
Guess I should have posted the original syntax version as well\.
Same tables and setup as above:
Same tables and setup as above:
select s.id, s.name, c.address_type, s.status
from academic_study s, address_current c
where s.id=c.id(+) and
s.name=c.name(+) and
c.address_type(+)='MA'
/
tip-a: Slowness of that query probably comes from the function call in the where clause of your query.
tip-b:you may want to ask how to get and use an "explain plan"
Now that you are using ANSI syntax, what you originally wanted is easier to achieve IMHO. The method you have used (a "derived table") is perfectly valid but you could simply use a "join condition"
tip-b:you may want to ask how to get and use an "explain plan"
Now that you are using ANSI syntax, what you originally wanted is easier to achieve IMHO. The method you have used (a "derived table") is perfectly valid but you could simply use a "join condition"
SELECT
ACADEMIC_STUDY.ACADEMIC_YEAR
, ACADEMIC_STUDY.ACADEMIC_YEAR_DESC
, ACADEMIC_STUDY.ACADEMIC_PERIOD
, ACADEMIC_STUDY.ACADEMIC_PERIOD_DESC
, ACADEMIC_STUDY.ID
, ACADEMIC_STUDY.NAME
, ADDRESS_CURRENT.STREET_LINE1
, ADDRESS_CURRENT.STREET_LINE2
, ADDRESS_CURRENT.ADDRESS_TYPE
, ADDRESS_CURRENT.CITY
, ADDRESS_CURRENT.STATE_PROVINCE
, ADDRESS_CURRENT.NATION_DESC
, ADDRESS_CURRENT.PREFERRED_ADDRESS_IND
, ADDRESS_CURRENT.COUNTY_DESC
, ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
'201330',
'N',
ACADEMIC_STUDY.Program) AS Calc_MaxTerm
FROM ODSMGR.DMC_V_ACADEMIC_STUDY_WITH_ATTR ACADEMIC_STUDY
LEFT OUTER JOIN ADDRESS_CURRENT
ON ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
AND ADDRESS_CURRENT.ADDRESS_TYPE = 'MA' --<< join condition
WHERE ACADEMIC_STUDY.ACADEMIC_PERIOD =
ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
'201330',
'N',
ACADEMIC_STUDY.Program) --<<<<<<<<<<<<<< probably cause of slowness
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STATUS IN ('AS', 'LA')
ORDER BY
ACADEMIC_STUDY.ACADEMIC_PERIOD
, ACADEMIC_STUDY.NAME
Thx Gentlemen,
sorry for the delayed response....
had to handle other things,
going to dig in to your sql code(s) now....
tx, sandra
sorry for the delayed response....
had to handle other things,
going to dig in to your sql code(s) now....
tx, sandra
Hi PortletPaul and SlightWV,
== OMG ==..... You have both taught me a concept that I never knew....
*** that you are allowed to code a WHERE-like condition in the LEFT join *** !!!!
I thought that the ON clause was simply to link the similar field-keys that the tables shared.
I just test the sql, and it worked, the rows that do not have MA, are being included in the results set, with NULL in the address... which is what i want.....
(I want to try this concept in Microsoft Access to see if they will allow it.....)
G-d bless everyone at EE, everyone on this post, and especially both of you for caring to dig deep to understand....
Now I will go apply this concept to my real SQL which is a little bigger....
**** Question, does this approach run faster than in line views/derived tables? just wondering....
@PortletPaul, yes the function probably takes time, as it get the MAX(Academic_Period) for a student to return the last term the student was registered in, this way I get one row, and not get cartesian product results....
tx Everyone, sandra
== OMG ==..... You have both taught me a concept that I never knew....
*** that you are allowed to code a WHERE-like condition in the LEFT join *** !!!!
I thought that the ON clause was simply to link the similar field-keys that the tables shared.
I just test the sql, and it worked, the rows that do not have MA, are being included in the results set, with NULL in the address... which is what i want.....
(I want to try this concept in Microsoft Access to see if they will allow it.....)
G-d bless everyone at EE, everyone on this post, and especially both of you for caring to dig deep to understand....
Now I will go apply this concept to my real SQL which is a little bigger....
**** Question, does this approach run faster than in line views/derived tables? just wondering....
@PortletPaul, yes the function probably takes time, as it get the MAX(Academic_Period) for a student to return the last term the student was registered in, this way I get one row, and not get cartesian product results....
tx Everyone, sandra
Hi Gentlemen,
am back... to just to crystallize:
@PortletPaul's idea:
use NASI left join = allowed to code a condition in the ON statement
@SlightWV's idea:
allowed to test a field from the left-joined-table == in the WHERE CLAUSE == ( in the traditional way of joining tables
using the plus sign)
tx, sandra
and ADDRESS_CURRENT.ADDRESS_TY
am back... to just to crystallize:
@PortletPaul's idea:
use NASI left join = allowed to code a condition in the ON statement
@SlightWV's idea:
allowed to test a field from the left-joined-table == in the WHERE CLAUSE == ( in the traditional way of joining tables
using the plus sign)
tx, sandra
and ADDRESS_CURRENT.ADDRESS_TY
Sandra, it is a great pleasure to assist. Cheers, Paul
btw: I prefer "ye olde" to describe the dark days before ANSI joins :-)
btw: I prefer "ye olde" to describe the dark days before ANSI joins :-)
oh, forgot to answer
**** Question, does this approach run faster than in line views/derived tables? just wondering....
ah! well, erhum, "it depends" I'm afraid is the prudent answer
Now you are using Oracle you have access to "explain plans" which give you insights into how a query is handled internally. If comparing different approaches it is useful to compare the explain plans. This topic however deserves more than a 3 sentence comment.
**** Question, does this approach run faster than in line views/derived tables? just wondering....
ah! well, erhum, "it depends" I'm afraid is the prudent answer
Now you are using Oracle you have access to "explain plans" which give you insights into how a query is handled internally. If comparing different approaches it is useful to compare the explain plans. This topic however deserves more than a 3 sentence comment.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.