mysql - SQL top records based on two tables relations -

There are three main items I am storing: articles, organizations, and keywords. This makes 5 tables:

  article {id} entity {ID, name} article_entity {id, article_id, entity_id} keyword {ID, name} article_keyword {id, article_id, keyword_id}  

I would include would like to receive all articles including top X keyword + institutions I top X keywords with a simple group entity_id / keyword_id or < / Strong> can get institutions

  SELECT [entity keyword | _ID, in relation to the number (*) as num_frame_nature_group by entity_id limit the number Diissi by ORDER 10  

headers and keywords I will get to all articles?

This was what I had imagined, but I know that it works by the organization because of the group, because this article has come back.

  SELECT * FROM where the article is present ([... where the article is mentioned in the top X entities ..]) and the current ([... where the article is mentioned in the top x keyword is ..]);  

If I believe that you keep the purpose of the query accurate then those articles The relationship with the top 10 institutions as well as one of the top 10 keywords is to find. If this is the case, then the following question should be done, through which the article has returned, there is a match in the set of top 10 institutions and the set of top 10 keywords.

Please a to insert it in an attempt to

  a.id = Join the article INNER from SELECT a.id article_entity ae ae.article_id INNER article_keyword ak. the id = ak.article_id iNNER jOIN (SELECT entity_id, COUNT (article_id)) as article_entity_count DESC by entity_id ordered by article_entity_count article_entity_count LIMIT 10) ae.entity_id = top_ae.entity_id inner joined top_ae (SELECT keyword_id, COUNT (article_id) in a.id by article_keyword_count DESC LIMIT 10) top_ak oN Ak.keyword_id = top_ak.keyword_id GROUP by article_keyword GROUP keyword_id ORDER from article_keyword_count as;  

The downside to using a simple limit 10 in two subqueries for top organizations / keywords is that it will not handle relations, so if The 11th keyword is popular as 10th, it will not be selected yet, it can be fixed using the ranking function, but anything in the Oracle or MSSQL Rack () window functions with AFQ MySQL Not be Is obtained.

I have set up a sample (but I'm lagging in the form of at least the data point and boundary 2 ).