title: SQL top records based on two tables relations
body: <p>I have three main items I am storing: Articles, Entities, and Keywords. This makes 5 tables:</p>
  
  <pre><code>article { id }
  entity {id, name}
  article_entity {id, article_id, entity_id}
  keyword {id, name}
  article_keyword {id, article_id, keyword_id}
  </code></pre>
  
  <p>I would like to get all articles that contain the TOP X keywords + entities. I can get the top X keywords <strong>or</strong> entities with a simple group by on the <code>entity_id/keyword_id</code>.</p>
  
  <pre><code>SELECT [entity|keyword]_id, count(*) as num FROM article_entity
  GROUP BY entity_id ORDER BY num DESC LIMIT 10
  </code></pre>
  
  <p><strong>How would I get all articles that have a relation to the top entities and keywords?</strong></p>
  
  <p>This was what I imagined, but I know it doesn't work because of the group by entity limiting the article_id's that return.</p>
  
  <pre><code>SELECT * FROM article
  WHERE EXISTS (
      [... where article is mentioned in top X entities.. ]
  ) AND EXISTS (
      [... where article is mentioned in top X keywords.. ]
  );
  </code></pre>