Home » SQL & PL/SQL » SQL & PL/SQL » Joining of Table A & B (Oracle 7.3, Unix)
Joining of Table A & B [message #329110] Tue, 24 June 2008 02:59 Go to next message
sunidhi
Messages: 36
Registered: January 2006
Location: New Delhi
Member
Dear Friends,

I am new to Oracle.Help me to prepare this report.

Suppose I have 2 tables :-

Table A :-

  ENO1   SAL1
  A23   500
  S14   300
  N34   100

Table B :-

  ENO2   SAL2
  L14   110
  T40    80
  A23   600 
  N34   150   

Final Report :-

  ENO1  SAL1  ENO2  SAL2

  A23   500   A23   600
  S14   300   Null  Null
  N34   100   N34   150
  null  null  T40   80 

Suppose I have two tables A & B

I have to join 2 tables A & B.To get final report like I had said above.

Thanks & Regards,

Sunidhi



[Mod-edit: Frank added code-tags to improve readability]

[Updated on: Tue, 24 June 2008 03:22] by Moderator

Report message to a moderator

Re: Joining of Table A & B [message #329111 is a reply to message #329110] Tue, 24 June 2008 03:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look up the documentation for Outer Joins. You can find the documentation at http://tahiti.oracle.com
Re: Joining of Table A & B [message #329113 is a reply to message #329110] Tue, 24 June 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

It is worth to read: JOINS

Regards
Michel
Re: Joining of Table A & B [message #329145 is a reply to message #329110] Tue, 24 June 2008 04:47 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> SELECT * FROM A;

ENO       SAL1
--- ----------
A23        500
S14        300
N34        100

SQL> SELECT * FROM B;

ENO       SAL2
--- ----------
L14        110
T40         80
A23        600
N34        150

SQL> SELECT A.ENO1,A.SAL1,
  2  B.ENO2,B.SAL2
  3  FROM A,B
  4  WHERE A.ENO1=B.ENO2(+)
  5  UNION
  6  SELECT A.ENO1,A.SAL1,
  7  B.ENO2,B.SAL2
  8  FROM A,B
  9  WHERE A.ENO1(+)=B.ENO2;

ENO       SAL1 ENO       SAL2
--- ---------- --- ----------
A23        500 A23        600
N34        100 N34        150
S14        300
               L14        110
               T40         80



regards,
Re: Joining of Table A & B [message #329152 is a reply to message #329145] Tue, 24 June 2008 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is a FULL OUTER JOIN operator.

Regards
Michel
Re: Joining of Table A & B [message #329153 is a reply to message #329152] Tue, 24 June 2008 05:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Not in V7 there isn't
Re: Joining of Table A & B [message #329155 is a reply to message #329153] Tue, 24 June 2008 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ooops! Embarassed Didn't see OP's version.

Regards
Michel

Re: Joining of Table A & B [message #329156 is a reply to message #329155] Tue, 24 June 2008 05:17 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Not like you Michel. Go get a coffee Smile
Previous Topic: Prob : Whats the difference?
Next Topic: SQL Query (n th Salary)
Goto Forum:
  


Current Time: Wed Dec 07 22:28:28 CST 2016

Total time taken to generate the page: 0.12902 seconds