Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Unanswered: Joining Tables with aggrigates

    I have 3 tables on my db, Projects, ProljectAllocationLog and Users

    Project consists of Projectid(PK), ProjectName
    ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, Users consists of UserID (PK), Fullname


    ProjectID ProjectName
    1234 ProjectOne
    2346 ProjectTwo
    7892 ProjectThree

    ProjectAllocationLogID ProjectID UserID
    1 1234 1
    2 2346 3
    3 2346 1
    4 1234 2
    5 7892 2
    6 1234 3
    7 7892 1
    8 7892 2

    UserID UserName
    1 Debbie Coates
    2 Fred Bloggs
    3 Jack Smith

    I want to be able to view a list of showing the most current User for each project

    eg

    ProjectID UserName
    1234 Jack Smith
    2346 DebbieCoates
    7892 Fred Bloggs



    SELECT Max(ProjectAllocationLog.projectAllocationLogID) AS MaxOfprojectAllocationLogID, ProjectAllocationLog.ProjectID
    INTO #TEMP
    FROM ProjectAllocationLog
    GROUP BY ProjectAllocationLog.ProjectID;

    SELECT [#TEMP].ProjectID, ProjectAllocationLog.UserID, Users.UserName
    INTO #TEMP2
    FROM Users INNER JOIN ([#TEMP] INNER JOIN ProjectAllocationLog ON [#TEMP].MaxOfprojectAllocationLogID = ProjectAllocationLog.projectAllocationLogID) ON Users.Userid = ProjectAllocationLog.UserID;

    Select * from #Temp2

    I have created this, which shows me the results I want, but think it is really clumsy, Is there a way of doing this so that I don’t need to create the Temp tables?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT P.ProjectID
         , PA.UserID
         , U.UserName
      FROM Projects as P
    INNER
      JOIN ProjectAllocationLog as PA
        ON PA.ProjectID = P.ProjectID
       AND PA.ProjectAllocationLogID =
           ( SELECT MAX(projectAllocationLogID)
               FROM ProjectAllocationLog 
              WHERE ProjectID = P.ProjectID )
    INNER
      JOIN Users as U
        ON U.Userid = PA.UserID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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