How SQL queries may be optimized

manpreet Best Answer 2 years ago

I have a SQL Server table Top_Research_Areas that contains data like 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 I want to select pid from another table i.e. sub_aminer_paper for the aid's in table Top_Research_Areas, whereas table sub_aminer_paper contains columns i.e. aid, pid, research_area, res_category_id and some more columns too.

Moreover Top_Research_Areas only contains records for top_3 research_area's whereas table sub_aminer_paper contains other than these records for aid's in Top_Research_Areas.

I have used this query i.e.

SELECT
    aid, pid, research_area
FROM
    sub_aminer_paper 
WHERE
    aid IN (2937, 11120)
    AND research_area IN (SELECT
                              research_area 
                          FROM 
                              Top_Research_Areas 
                          WHERE
                              aid IN (2937, 11120))
ORDER BY aid ASC

Now the issue is, when retrieving pid's from sub_aminer_paper by matching research_area's in both tables, it gives me output e.g. if I retrieve records for two aid's i.e. 2937 and 11120, it gives me the output as:

We can see that the Paper_Count for Top 2 aid's are 3+1+1+4+2+2 i.e. it should give 13 records, but it is giving 14 because of research_area i.e. optimization problem actually belongs to aid i.e. 11120 in table Top_Research_Areas but by using IN clause for matching research_area it is taking as a mixture of research_area's of both aid's, whereas I need 13 records in output instead of 14.

How can it be handled ?

Please help and thanks!