Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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)


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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.