Drupal 8: Creating A Subquery In Views
You've probably been there before. A Drupal View you are working on looks great and has all the data and fields you need, but when you look a little closer at the results you realise that something is off. After looking at the generated SQL query you see that there is a problem with one of the joins which causes your counts to be off a little. Ultimately, you need to remove this join, but you really need that data included in your results.
Creating a subquery can allow you to pull out data from a specific field without adding another join that would cause the results to be out.