Home » SQL & PL/SQL » SQL & PL/SQL » Help in Join (oracle 9)
Help in Join [message #321506] Tue, 20 May 2008 09:19 Go to next message
siprem
Messages: 4
Registered: May 2008
Junior Member
Hi All,
I have 2 tables A and B. A contains the following data
select * from A
1
2
3
4
5
6
7
..

select * from B
1
3
5
7

Now I need a query which should output as below
1
No data present
3
No data present
..
I need to write a join query which would fetch the matching data in both the tables and data from table A with which doesn't match.

Please help me!!!
Re: Help in Join [message #321507 is a reply to message #321506] Tue, 20 May 2008 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Help in Join [message #321510 is a reply to message #321507] Tue, 20 May 2008 09:38 Go to previous messageGo to next message
siprem
Messages: 4
Registered: May 2008
Junior Member
Hi,
Do I need to elaborate the question?
I have 2 tables table1 and table2. Table1 contains say 100 records and table2 contains 50 records. There is one common column in both the table on which I am trying to match in where condition. But the problem is other than the matched data I also need to display the unmatched records in table1 which needs to be substituted by 'No data found' string.
I tried to do this by left outer join.
Please hellp me as I am new to sql.
Re: Help in Join [message #321514 is a reply to message #321510] Tue, 20 May 2008 09:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use an outer join and use a NVL on the nullable column
Re: Help in Join [message #321517 is a reply to message #321514] Tue, 20 May 2008 10:14 Go to previous messageGo to next message
siprem
Messages: 4
Registered: May 2008
Junior Member
But the left outer join returns more values than the number of records in table 1
Re: Help in Join [message #321518 is a reply to message #321517] Tue, 20 May 2008 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
siprem wrote on Tue, 20 May 2008 17:14
But the left outer join returns more values than the number of records in table 1

No, outer join NEVER returns more rows.

SQL Reference, Joins

Regards
Michel

Re: Help in Join [message #321523 is a reply to message #321506] Tue, 20 May 2008 10:25 Go to previous messageGo to next message
siprem
Messages: 4
Registered: May 2008
Junior Member
Ok I will try to explain more

Here are the two tables:

select * from table1;
NAME
A
B
C
D
E
.
.
Z

select * from table2;
NAME,ID
A,1
C,3
E,5

The output should be
NAME,ID
A,1
NO DATA,
C,3
NO DATA,
E,5
NO DATA
NO DATA
.
.
for the remaining records in table1.

Other than the matched column it should display the remaining records in the table1 with the value substituted by 'NO DATA'
Re: Help in Join [message #321527 is a reply to message #321523] Tue, 20 May 2008 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this has been answered, try to understand the answers now.

Regards
Michel
Re: Help in Join [message #321631 is a reply to message #321510] Tue, 20 May 2008 22:17 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Siprem -
Per Michel and Frank's responses which contain the answers,

there are two points in the solution that they provided for you to key in on and develop this:

#1 The data in the outer join table will be NULL when a value for name from table1 does exist in table2. In that instance
table1.name and table2.id will be null.

Start with writing/executing the outer join correctly so that you see the {null} output returned for a visual perspective.


#2 Look up the syntax for NVL() function which is how you will transform the Null to 'No Data'. eg. Select NVL(null,'I am null') from dual;

I only elaborate as such since you said that you 'tried using a left outer join' with no further examination. Get a successful outer join query working first and foremost, then apply NVL().

Working something out on your own is key to overcoming any learning curve and absorbing the next topic quicker. Smile

Best Regards,
Harry
Previous Topic: How to convert a file in one characterset to another using pl/sql
Next Topic: How to put a group of columns from multiple rows into one row dynamically
Goto Forum:
  


Current Time: Sat Dec 03 14:22:17 CST 2016

Total time taken to generate the page: 0.08920 seconds