Post reply

Strange Thing

  • June 25, 2006 at 10:31 am

    #114563

    i'm trying to get all the user from database SQL Server 2000 through this but some (not all) of the data is duplicated.

    SELECT Users.userId, userName, userPassword, Users.branchId, UserGroups.groupId FROM Users LEFT JOIN UserGroups ON Users.branchId=UserGroups.branchId

    UserGroups have 3 attributes: 1. userId 2.branchId 3. groupId

    Users have following attributes: 1.userId 2.branchId 3.userName 4. userPassword

    i've test in query analyzer but for one record; two rows are shown i.e.duplicated data. and the message comes "Two rows are affected". In database; only one row is present to a particular data.

    can u identify what's wrong with this query??

  • June 26, 2006 at 3:06 pm

    #645930

    any idea?

  • June 26, 2006 at 3:44 pm

    #645935

    I am not quite clear on what you are after here, but from a quick glance, you need to check your join columns.  Why are you joining on branchID only? 

    Is your UserID column unique?  If so, do your LEFT JOIN ON Users.UserID = UserGroups.UserID.  If UserID is not null, you may need to join on UserID and BranchID. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 26, 2006 at 3:50 pm

    #645938

    in the table both; userId and branchId are Part of PK that's composite key. how can i join on both these keys??

  • June 26, 2006 at 4:50 pm

    #645945

    SELECT Users.userId, userName, userPassword, Users.branchId, UserGroups.groupId

    FROM Users JOIN UserGroups

    ON Users.branchId=UserGroups.branchId

    AND Users.UserId = UserGroups.UserID

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply