Accidental duplicate results...

ALTER PROCEDURE discussions_GetTopics
(@.board_id as int)
AS

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Topics.topic_id = discussions_Posts.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_1.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

I am simply trying to return a result for each topic, that has user info for both the author of the topic and the author of the last post (user_1, user_2)

The problem is, it will return multiple datarows with the same topic, and each of them have a different last post author.. when there can only be one last poster... idk.. im confused.. help?

Try:

ALTER PROCEDURE discussions_GetTopics(@.board_idas int)ASSELECT discussions_Topics.*, discussions_Posts.*, user_1.UserNameAS Topic_Author_Username, user_1.UserIdAS Topic_Author_ID, user_2.UserNameAS Post_Author_Username, user_2.UserIdAS Post_Author_IDFROM discussions_TopicsINNERJOIN discussions_PostsON discussions_Topics.topic_id = discussions_Posts.topic_idINNERJOIN aspnet_UsersAS user_1ON user_1.UserId = discussions_Topics.topic_posterINNERJOIN aspnet_UsersAS user_2ON user_2.UserId = discussions_Topics.last_poster_idWHERE (discussions_Topics.board_id = @.board_id)

|||

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Posts.topic_id = discussions_Topics.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

In the Topics table, I did not have a field for "last_poster_id". I only have a "last_post_id" to use to search the "Posts" table to find the author of the post.

The problem with this query is that it returns a seperate topic for each user that has posted in the topic as the last poster. any help?

|||

WAIT!

GOT IT!

ALTER PROCEDURE discussions_GetTopics
(@.board_id as int)
AS

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

Dumb noob mistake.. lol... im still learning...