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', 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;