Post reply

Outer join returning more rows than in the table!

  • October 21, 2008 at 12:23 am

    #194777

    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

    #887437

    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

    #887439

    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

    #887444

    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

    #887448

    use inner join to get exact result.

  • October 21, 2008 at 1:23 am

    #887453

    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

    #887465

    avoid leftouterjoin, and match the all common columns in where clause

    or use innerjoin

    guru

  • October 21, 2008 at 2:31 am

    #887466

    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