Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Question
Outer Join Question [message #669780] Fri, 11 May 2018 07:10 Go to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
I have two tables



table1

---------

empno varchar2(5)

year varchar2(4)

field1 number(2)

field2 number(2)



table2

----------

empno varchar2(5)

year varchar2(4)

field3 number(2)

field4 number(4)



The primary key in both tables is (empno,year)



I have the following stats:



select count(*) from table1 where year='2013';

result: 195

select count(*) from table2 where year='2013';

result: 555



the inner join produces:



select count(*) from table1,table2 where table1.empno=table2.empno

and table1.year='2013' and table2.year='2013;

result: 40



so if I am to do an outer join between table1 and table2 on empno and year='2013'

I should get : 195+555-40=710



but when I run the following commands:



select count(*) from table1 full join table2 on table1.empno=table2.empno and

table1.year='2013' and table2.year='2013;

result:3584



select count(*) from table1 full join table2 on table1.empno=table2.empno where

table1.year='2013' and table2.year='2013;

result:40



both not producing the correct result.



What is the correct syntax for the outer join command given the table structure above? and,

what would be the correct syntax if there was a third table with the following structure



table 3

----------

empno varchar2(5)

year varchar(4)

field5 number(2)

field6 number(2)



again the primary key is (empno,year)



Thank you.
Outer Join Question [message #669781 is a reply to message #669780] Fri, 11 May 2018 07:14 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
I have two tables



table1

---------

empno varchar2(5)
year varchar2(4)
field1 number(2)
field2 number(2)



table2

----------

empno varchar2(5)
year varchar2(4)
field3 number(2)
field4 number(4)



The primary key in both tables is (empno,year)

I have the following stats:

select count(*) from table1 where year='2013';

result: 195

select count(*) from table2 where year='2013';

result: 555



the inner join produces:

select count(*) from table1,table2 where table1.empno=table2.empno
and table1.year='2013' and table2.year='2013;

result: 40



so if I am to do an outer join between table1 and table2 on empno and year='2013'
I should get : 195+555-40=710



but when I run the following commands:



select count(*) from table1 full join table2 on table1.empno=table2.empno and
table1.year='2013' and table2.year='2013;

result:3584



select count(*) from table1 full join table2 on table1.empno=table2.empno where
table1.year='2013' and table2.year='2013;

result:40

both not producing the correct result.


What is the correct syntax for the outer join command given the table structure above? and,
what would be the correct syntax if there was a third table with the following structure

table 3

----------

empno varchar2(5)
year varchar(4)
field5 number(2)
field6 number(2)

again the primary key is (empno,year)

Thank you.
Re: Outer Join Question [message #669783 is a reply to message #669781] Fri, 11 May 2018 07:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4144558
Re: Outer Join Question [message #669784 is a reply to message #669780] Fri, 11 May 2018 07:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topics:

Michel Cadot wrote on Sat, 22 September 2012 16:11
Michel Cadot wrote on Thu, 20 September 2012 11:55
Michel Cadot wrote on Wed, 19 September 2012 12:36
It would be better to understand that you post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

cookiemonster wrote on Tue, 22 September 2015 11:42
...
And always use code tags for code.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.


Also why did not you NEVER feedback and thank people who spent time to help you?

Join

[Updated on: Fri, 11 May 2018 08:00]

Report message to a moderator

Previous Topic: FORALL Bulk insert using variable
Next Topic: Bulk collect for inserting million of records
Goto Forum:
  


Current Time: Thu Mar 28 07:49:43 CDT 2024