Outer join returning more rows than in the table!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Outer join returning more rows than in the table!

Author

Topic

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-10-21 : 02:23:54

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)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-21 : 02:28:51

there may be multiple records present in Client table for single Client_panno.
That is why u r getting more noofrecords than the actual.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 02:30:42

it means you second table is having many to one relationship with clientcode table. just run this to get id with duplicate records existing in client


select client_panno from client group by client_panno having count(*) >1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 02:32:58

may be this is what you're looking at

select cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
,MAX(c.Client_panno),MAX(c.client_userid)
from clientcode cc LEFT outer join client c 
on cc.cc_panno=c.client_panno 
group by cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
order by cc_clientcode

if not show some sample data and illustrate what values you expect in case of more than 1 record existing

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-10-21 : 02:41:28

quote:
Originally posted by visakh16

may be this is what you're looking at

select cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
,MAX(c.Client_panno),MAX(c.client_userid)
from clientcode cc LEFT outer join client c
on cc.cc_panno=c.client_panno
group by cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
order by cc_clientcode

if not show some sample data and illustrate what values you expect in case of more than 1 record existing

I want actually want records which return null values in c.Client_panno i.e.,
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 02:52:21

then use this

select cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname,
MAX(c.client_userid)
from clientcode cc LEFT outer join client c 
on cc.cc_panno=c.client_panno 
WHERE c.Client_panno IS NULL
group by cc.cc_clientcode,cc.cc_rmcode,cc.cc_panno,cc.cc_productname
order by cc_clientcode

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-10-21 : 03:23:33

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources