sql server - How SQL queries may be optimized -

i have sql server table top_research_areas contains data i.e.

aid     res_category_id    research_area            paper_count      --------------------------------------------------------------- 2937    33                 markov chain             3                2937    33                 markov decision process  1                2937    1                  linear system            1                11120   29                 aspect oriented prog     4                11120   1                  graph cut                2                11120   1                  optimization problem     2                12403   2                  differential equation    7                12403   1                  data structure           2                12403   1                  problem solving          1                35786   1                  complete graphs          11               35786   1                  graph cut                10               35786   null               null                     2                49261   3                  finite automata          6                49261   3                  finite element           2                49261   14                 database                 2                 78841   5                  genetic programming      6                78841   23                 active learning          2                78841   28                 pattern matching         1                  

now want select pid table i.e. sub_aminer_paper aid's in table top_research_areas, whereas table sub_aminer_paper contains columns i.e. aid, pid, research_area, res_category_id , more columns too.

moreover top_research_areas contains records top_3 research_area's whereas table sub_aminer_paper contains other these records aid's in top_research_areas.

i have used query i.e.

select     aid, pid, research_area     sub_aminer_paper      aid in (2937, 11120)     , research_area in (select                               research_area                                                           top_research_areas                                                          aid in (2937, 11120)) order aid asc 

now issue is, when retrieving pid's sub_aminer_paper matching research_area's in both tables, gives me output e.g. if retrieve records 2 aid's i.e. 2937 , 11120, gives me output as:

we can see paper_count top 2 aid's 3+1+1+4+2+2 i.e. should give 13 records, giving 14 because of research_area i.e. optimization problem belongs aid i.e. 11120 in table top_research_areas using in clause matching research_area taking mixture of research_area's of both aid's, whereas need 13 records in output instead of 14.

how can handled ?

please , thanks!

there paper on "optimization problem" aid 2937 isn't logged in top_research_areas.

see id helps : select sub_aminer_paper combination of (aid,research_area) exists,

select     sap.aid, sap.pid, sap.research_area     sub_aminer_paper  sap   sap.aid in (2937, 11120) --- indexing i'm assuming column has   , exists (select 1 top_research_areas tra tra.aid = sap.aid , tra.research_area = sap.research_area , tra.aid in (2937,11120))