I want to create a query in SE Data Explorer which does the follows:
Get all questions which have (at least) an answer by a specified user and with no accepted answer (by anyone).
I have never written such code and the documentation is lacking. Here is the line I mustered from examples and the slight documentation there is:
SELECT a.id AS [Post Link] FROM Posts a WHERE a.OwnerUserId = ##UserId## and PostTypeId = 2
This Gets all questions which have (at least?) an answer by a specified user, but I can't manage to filter only those with no accepted answer.
The type Posts has AcceptedAnswerId int, but no matter what int I put, I get nothing back. I couldn't even find what this property means or what values are legitimate. For PostTypeId I just tried and found that 1 is questions and 2 is answers.
I have also looked at the type Type question and found accepted_answer_id with no documentation as to what it is. Moreover, I don't understand how to use these question types (using FROM Questions gives an error) or if these the right way to go. I do know I retrieve them by calling one of of the "Methods That Return This Type".
Lastly, I wonder if what I wrote is even a correct approach. Is the order in which filters are applied matter or do they apply simultaneously? What is the "best practice" way of doing the call I want?
At the moment you only look at the posts of a user. But you need to get the AcceptedAnswerId field of the corresponding question. This field qoul be null if the question has no accepted answer until now. I edited your query by adding a JOIN on the Posts table to get the question and the questions' AcceptedAnswerId field.
SELECT q.Id as [Post Link]
FROM Posts a
JOIN Posts q on a.ParentId = q.Id
WHERE a.OwnerUserId = ##UserId##
and a.PostTypeId = 2
and q.AcceptedAnswerId is null
At this point you could also exclude closed questions by adding and ClosedDate is null
You can also find and try my solution at the dataexplorer.