Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

How SQL queries may be optimized

General Tech Learning Aids/Tools

Max. 2000 characters
Replies

usr_profile.png

User

( 5 months 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. aidpidresearch_areares_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:

enter image description here

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!

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif