Results 1 to 5 of 5

Thread: query

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: query

    hello everybody
    i have following tables

    1. Role
    roleId
    roleName

    2.User
    userId
    userName

    3 Team
    userId
    userTLID
    isActive


    Team table maintains user team history
    i have to find out user with his current TLId i am creating a view
    create view selUsr as
    SELECT dbo.[user].userID, dbo.[user].userName, dbo.[user].roleID, dbo.role.roleName, user1.userName AS tl
    FROM dbo.role INNER JOIN
    dbo.[user] ON dbo.role.roleID = dbo.[user].roleID LEFT OUTER JOIN
    dbo.Team ON dbo.[user].userID = dbo.Team.userID LEFT OUTER JOIN
    dbo.[user] AS user1 ON user1.userID = dbo.Team.TLID
    but its giving duplcate rows like giving all entries from team table


    Plz help me out
    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe all you need is?
    where isActive=1

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    thanks for reply..
    i have to select tlid whether it is active or not.ok.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Please clarify
    Quote Originally Posted by meghamaharshi
    i have to find out user with his current TLId
    And
    Quote Originally Posted by meghamaharshi
    i have to select tlid whether it is active or not.ok.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Perhaps you could give us the CREATE table scripts and some INSERT scripts for examples. At first sight your code looks fine.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •