User Posts (Download-Friendly)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Iota

Q&A for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain

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',
    qu.DisplayName AS 'Asker',
    qu.Reputation AS 'AskRep',
    IIF(p.PostTypeId = 1, 'Q', 'A') AS 'Type', -- 1 = question, 2 = answer...
    p.CreationDate AS 'Asked',
    p.Score AS 'Q Score',

    --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

    q.ViewCount AS 'Views',
    q.AnswerCount AS 'Answers',
    au.DisplayName AS 'Answerer',
    au.Reputation AS 'AnsRep',
    a.Score AS 'A Score',
    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 'My 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;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results