Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 28th, 2011, 04:15 AM
Authorized User
Points: 350, Level: 6
Points: 350, Level: 6 Points: 350, Level: 6 Points: 350, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: , , Pakistan.
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default Logical issue in 'Outer join', why out join is not working, the way it should?

I am using outer join, I have two tables tblaccount with 33 records and tblcustchannelacct with 146 records, and I am writing a query to have only tblaccounts matching records with tblcustchannelacct, so only 33 records should come, but no matter how much I change the query I get 146 records.
Queries are below:
*-Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id (+) = tab2.account_id;
Here I used (+) in the WHERE clause for tblaccount to include all the rows that is tblaccount but 146 i.e.: tblcustchannelacct no. of records are showing.
Now I used outer join with keyword of ‘outer join’ instead of (+)
*-Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblcustchannelacct tab2
left join tblaccount tab1
on tab1.ACCOUNT_ID= tab2.ACCOUNT_ID
LEFT JOIN keyword returns all rows from the left table tblaccount, even if there are no matches in the right table tblcustchannelacct.
(but showing the same 146 records of tblcustchannelacct).
__________________
How to do programming?
  #2 (permalink)  
Old March 1st, 2011, 01:48 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi,

if you only want records matching tblaccounts and tblcustchannelacct you should use an inner join:
Code:
 
Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id = tab2.account_id;
Further, the old Oracle join syntax is a bit confusing. What you wrote was actually a right outer join (so you indeed got all records from tblcustchannelacct and from tblaccounts only those that match). If you want to have all records from tblaccounts and only those from tblcustchannelacct you need a left outer join as follows:
Code:
 
Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id = tab2.account_id(+);
Just think of the plus-sign as meaning "fill all the blanks for me in, will you"..

I hope this helps.
  #3 (permalink)  
Old March 7th, 2011, 02:05 AM
Authorized User
Points: 350, Level: 6
Points: 350, Level: 6 Points: 350, Level: 6 Points: 350, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: , , Pakistan.
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default No matter where I use (+), I still get tblcustchannelacct

@disel2010: No matter where I use (+) on the right side or the left side, I still get tblcustchannelacct no. of records


Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id = tab2.account_id(+);

gives exactly the same answer as:

Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id (+) = tab2.account_id;

whereas it should give only 33 records that tblaccount has.
__________________
How to do programming?
  #4 (permalink)  
Old March 7th, 2011, 04:34 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi arbab,

and does the inner join (without the plus sign) also give you the same number?
Then you are probably just looking at duplicate entries of the same account_id... (guessing the relation between tblaccount and tblcustchannelacct is 1-n)

Try this to see how many unique account_id's you have:
Code:
SELECT DISTINCT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id;
  #5 (permalink)  
Old March 7th, 2011, 07:17 AM
Authorized User
Points: 350, Level: 6
Points: 350, Level: 6 Points: 350, Level: 6 Points: 350, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: , , Pakistan.
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default With 'Distinct' or without 'Distinct'

Dear disel2010:

select count (*) from tblaccount
Ans: 38 rows

select count (*) from tblcustchannelacct
Ans:155 rows

SELECT DISTINCT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id;

Ans: 38 rows

After removing 'Distinct':

SELECT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id;

Ans: 155 rows

Now no matter where I use (+):

SELECT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id (+);

or

SELECT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id (+)= tab2.account_id;

the answer is same: 155

Conclusion: So left or right outer joins do not work when there is duplication of records?
__________________
How to do programming?
  #6 (permalink)  
Old March 7th, 2011, 09:22 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi arbab,


the outer join gives the same result as the inner join because all your primary keys of tblaccount are present in tblcustchannelacct.

(As said before, an outer join works as "fill all the blanks for me in, will you"...
and here you don't have missing joins so also no blanks)
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
left outer join rokitsalad LINQ 0 January 21st, 2009 11:05 AM
left outer join keyvanjan Classic ASP Basics 1 April 15th, 2006 05:37 AM
left outer join keyvanjan Classic ASP Professional 0 February 5th, 2006 11:54 AM
Outer Join, 2 columns jking SQL Language 1 December 5th, 2004 04:14 AM
outer join on same table roog SQL Language 4 September 30th, 2004 05:31 AM



All times are GMT -4. The time now is 04:05 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.