October 21, 2008 at 12:23 am
The two queries are returning two different no of rows.Outer join is not supposed to duplicate the rows in the first table right?How do I correct this.
select * from clientcode order by cc_clientcode
result:(5627 row(s) affected)
select cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
,c.Client_panno,c.client_userid
from clientcode cc LEFT outer join client c
on cc.cc_panno=c.client_panno order by cc_clientcode
Result:(5638 row(s) affected)
October 21, 2008 at 12:35 am
nithin.gujjar (10/21/2008)
The two queries are returning two different no of rows.Outer join is not supposed to duplicate the rows in the first table right?How do I correct this.
select * from clientcode order by cc_clientcode
result:(5627 row(s) affected)
select cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
,c.Client_panno,c.client_userid
from clientcode cc LEFT outer join client c
on cc.cc_panno=c.client_panno order by cc_clientcode
Result:(5638 row(s) affected)
it is also displaying the un-matched rows from the client table. use inner join for the perfect matching between both the tables.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 21, 2008 at 12:39 am
But I want something like this where only null values are returned
select cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
,c.Client_panno,c.client_userid
--,u.user_userid
from clientcode cc LEFT outer join client c
on cc.cc_panno=c.client_panno order by cc_clientcode
where c.client_panno is null
October 21, 2008 at 12:49 am
Where clause should come before the Order by.
Once thats done check whthr the query is executing as u want. If not give us some more details.
"Keep Trying"
October 21, 2008 at 12:55 am
use inner join to get exact result.
October 21, 2008 at 1:23 am
Thanks all, the problem was client table had multiple rows with with same client_panno that's a logical error.We are not supposed to have it that way.
October 21, 2008 at 2:25 am
avoid leftouterjoin, and match the all common columns in where clause
or use innerjoin
guru
October 21, 2008 at 2:31 am
But that way I will not get null values in join column of other table
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy