1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people organizing, marketing or licensing open source development projects

DECLARE @UserId INT = ##UserId:INT##;

SELECT
-- p - Posts Table
-- u - Users Table

    ROW_NUMBER() OVER (ORDER BY p.Id ASC) AS 'ID',
    --p.Id AS [Post Link],
    q.Title AS 'Question',
    IIF(p.PostTypeId = 1, 'Q', 'A') AS 'Type', -- 1 = question, 2 = answer...
    qu.DisplayName AS 'Asker',
    qu.Reputation AS 'AskRep',
    q.Score AS 'Q Score',
    q.CreationDate AS 'Asked',

    --p.OwnerUserId,
    --u.DisplayName, -- after the 1st JOIN
    -- instead of: 
    --p.OwnerDisplayName, -- NULL, why does it exist?

    --u.Id, -- = p.OwnerUserId (the 1st JOIN)
    --u.AccountId, -- StackExchange Account Number

    p.Score AS 'P Score',
    q.ViewCount AS 'Views',
    q.AnswerCount AS 'Answers',
    au.DisplayName AS 'Answerer',
    au.Reputation AS 'AnsRep',
    a.Score AS 'AA Score',
    p.CreationDate AS 'Answered',
    q.CommunityOwnedDate AS 'Community Owned',
    q.ClosedDate AS 'Closed',
    -- How does 'PostsWithDeleted' work?
    --q.DeletionDate AS 'Deleted', -- NULL even with 'PostsWithDeleted' !?
    q.Tags AS 'Tags', -- How to split into columns?

    -- questions only 
    --p.AcceptedAnswerId, -- questions only
    --p.ViewCount, -- questions only
    --p.CommunityOwnedDate, -- questions only
    --p.ClosedDate, -- questions only
    --p.DeletionDate, -- questions only: doesn't work!?
    --p.Title, -- questions only (is in 'p.Id AS [Post Link]')
    --p.Tags, -- questions only
    
    -- IDs (to create links)
    IIF(p.ParentId IS NULL,p.Id,p.ParentId) AS 'Question ID', -- questions
    q.AcceptedAnswerId AS 'Acc. Answer ID', -- accepted answers
    p.Id AS 'Post ID', -- user's questions and answers
    qu.Id AS 'Asker ID',
    au.Id AS 'Answerer ID'

FROM Posts AS p
JOIN Users AS u ON p.OwnerUserId = u.Id
-- Needs a left join to not loose the answers!?
LEFT JOIN Posts AS q ON IIF(p.ParentId IS NULL, p.Id, p.ParentId) = q.Id
LEFT JOIN Users AS qu ON qu.Id = q.OwnerUserId 
LEFT JOIN Posts AS a ON q.AcceptedAnswerId = a.Id 
LEFT JOIN Users AS au ON au.Id = a.OwnerUserId

WHERE p.OwnerUserId=@UserId

ORDER BY p.Id ASC;

fork query

edited mar 21

created feb 27 VBasic2008