Results 1 to 8 of 8

Thread: Query help! (usernames)

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Query help! (usernames)

    I am having a little bit of trouble! Today is not my day at all. Can't think straight!

    I want to be able to get the last username who replied. This is what I have but it is incorrect, as I always get the same username for each record, when this is not true

    Code:
    
    SELECT Threads.[ID] 'ThreadID', ThreadName, DateOfCreation 'DateCreated', 
    (SELECT TOP 1 DateOfReply FROM Replies WHERE Replies.ThreadID = Threads.[ID] ORDER BY DateOfReply DESC) 'LastReplyDate',
    (SELECT TOP 1 e.username FROM Replies, Users WHERE Replies.UserID = Users.[ID] ORDER BY DateOfReply DESC) 'LastReplyUsername', --HERE
    (SELECT COUNT(ReplyID) FROM Replies WHERE ThreadID = Threads.[ID]) 'NumberOfReplies',
    
    e.username 'UsernameCreator'
    FROM Threads
    
    INNER JOIN Users e ON
    e.[ID] = ThreadStarterUser

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Query help! (usernames)

    You arent relating that SQL statement back to the main one (as you are with the Date equivalent).

    Presumably all you need to do is add this to the Where clause: AND Replies.ThreadID = Threads.[ID]

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Re: Query help! (usernames)

    are you talking about adding that Where clause in the 'LastUsernameReply' field?
    would this actually give me the last username to reply in the thread, as I have done with the DateOfReply (LastDateReply)?

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Query help! (usernames)

    That's what I meant, and I assume that it would... but then I have had a couple of drinks

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Re: Query help! (usernames)

    lol
    and no that didnt work

    i think i can officially declare that you are not on the same planet as SQL

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Re: Query help! (usernames)

    this seems to work:

    Code:
    SELECT Threads.[ID] 'ThreadID', ThreadName, DateOfCreation 'DateCreated', 
    (SELECT TOP 1 DateOfReply FROM Replies WHERE Replies.ThreadID = Threads.[ID] ORDER BY DateOfReply DESC) 'LastReplyDate',
    (SELECT TOP 1 a.Username FROM Replies 
    
    INNER JOIN Users a ON
    a.[ID] = Replies.UserID
    
    WHERE Replies.ThreadID = Threads.[ID] AND a.[ID] = Replies.UserID 
    
    ORDER BY DateOfReply DESC) 'LastUserReplied',
    (SELECT COUNT(ReplyID) FROM Replies WHERE ThreadID = Threads.[ID]) 'NumberOfReplies',
    
    e.username 'UsernameCreator'
    FROM Threads
    
    INNER JOIN Users e ON
    e.[ID] = ThreadStarterUser

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Query help! (usernames)

    Strange.. that's basically the same as I suggested (just better Join syntax, and use of the table alias for Users). Ah well, at least it's working now!

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Re: Query help! (usernames)

    Thanks Si

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

Posting Permissions

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



Click Here to Expand Forum to Full Width