Q&A for Joomla! administrators, users, developers and designers
--p.ViewCount, a.Id, a.Reputation, a.DisplayName as QuestionAsker, p.CreationDate as DateAsked, p.Id, p.Title, p.AnswerCount, p.Score as QuestionScore, b.Id, b.Reputation, b.DisplayName as Answerer, answer.CreationDate as DateAnswered, answer.Score as AnswerScore, t.TagName,
a.id,a.DisplayName as QuestionAsker,a.Reputation,p.CreationDate,p.id,p.Title,b.id,b.DisplayName as Answerer, b.Reputation,
WHEN ((p.AcceptedAnswerId = answer.Id)) THEN 'selected answer'
ELSE 'attempted answer'
END as answertype
FROM Users a, Posts p, Users b, Posts answer, PostTags pt, Tags t
WHERE p.OwnerUserId = a.Id -- or accountId?
AND t.TagName in ('r')
AND answer.OwnerUserId = b.Id -- or accountId
AND p.PostTypeId = 1 -- question
AND answer.PostTypeId = 2 -- answer
AND Pt.PostId = p.id
AND t.Id = pt.TagId
AND answer.parentid = p.Id
AND p.CreationDate >= '2012-01-01'
AND p.CreationDate < '2013-01-01'
ORDER by p.CreationDate asc, answertype desc;
-- For answered questions
-- give me
-- the question
-- the selected answer
-- the non-selected answers
-- the basic ids, scores of each, and their authors
-- for the language R
-- ordered by creation date