SO Dataset query to retrieve questions, related answers and comments with a specific question tag

Question

For a small part of my master thesis I need to retrieve a dataset from SEDE that contains all questions as well as the related multiple answers (and if possible also comments) with the tag "text-mining" in order to put questions and related answers (and likely also the comments) in a corpus.

My created queries won't work and SEDE is sometimes just not executing the commands due to a time lack. I already tried the adapted suggestions from SEDE but there is nothing sufficient.

For example:

SELECT
  P.Id,
  P.ParentId,
  P.CreationDate,
  P.ViewCount,
  P.Title,
  P.Body,
  P.Tags,
  P.PostTypeId
FROM
  Posts as P
WHERE
  P.PostTypeId = 1 OR P.PostTypeId = 2 AND P.tags LIKE '%<text-mining>%' 

Here only questions were returned (PostTypeId = 1). I think that might depend on the absence of tags for Type 2 (answers) but how can I retrieve a dataset including both - questions and related answers?

Answer 1

You're right, tags (and title and number of views) are only stored for questions, not answers. That means that for answers, you need to JOIN the Posts table with itself, like this query.

Because the size of the Stack Overflow database is quite large, it might be helpful to introduce more joins to PostTags and Tags instead of relying on a LIKE operator in the WHERE clause.

Comments are stored in a different table, Comments, so you'd need another JOIN. I hope you're able to do this yourself; you seem to have at least a basic grasp of SQL and if not, you're always welcome to follow the tutorial written by Monica Cellio.

Answer 2

As already mentioned by Glorfindel, one thing which could make your queries faster is using the table PostTags. Some examples: questions, answers. (Notice that for answers we have to use ParentId and then check the tags of the corresponding question.)

You could use COALESCE to get both questions and answers. For example, here are both questions and answers in a given tag and comments on them.

I have used a relatively small site in the above examples. (In part, this is because you originally asked about this on that site's meta. Of course, you can try the same query on any site and use any tag.) If you are doing this on a big site and with a tag which contains many posts, it is quite possible that the queries might time out. You might also run into the problem that SEDE won't return more than 50000 results - and split the whole thing into several smaller queries.

Once you run into the problems with the size of your result set, maybe it is worth considering whether using the data dump instead of SEDE would be an option. The data dump can be downloaded from Internet Archive. (In this way you would get the database containing all questions, answers, comments at some point as an XML-file.)

However, if you're interested in the tag text-mining on SO, that could be small enough to be handled using SEDE. Here are the queries I linked above run on SO for this tag: questions, answers, questions and answers, comments. (However, when I tried the same queries on some of the large tags on SO, they failed.)