CODEWITHSUNDEEP
sql-serverjoin
maliks
maliks

Reputation: 1112

How to multi JOIN in SQL Server

I have a query in SQL Server as: 

SELECT MD.Author_ID, MD.CoAuthor_ID, MD.Venue_ID, A2P.pid [Paper_ID], 
       P.abstract_research_area_category_id [Paper_Category_ID], 
       MD.Year
FROM Merged_Data MD
JOIN sub_aminer_author2paper A2P ON MD.Author_ID = A2P.aid AND
                                    MD.Year      = A2P.p_year AND
                                    MD.Venue_ID  = A2P.p_venue_vid
JOIN sub_aminer_paper P          ON MD.Venue_ID  = P.p_venue_vid AND
                                    MD.Year      = P.p_year
WHERE MD.Author_ID = 677

Whereas I'm unable to get desired results because unable to join A2P.pid with sub_aminer_paper for extracting [Paper_Category_ID].

How can I join A2P.pid with sub_aminer_paper to have a pid match and extract [Paper_Category_ID] whereas sub_aminer_paper has the field pid?

Upvotes: 2

Views: 99

Answers (2)

bmsqldev
bmsqldev

Reputation: 2735

Try This,

    SELECT MD.Author_ID, MD.CoAuthor_ID, MD.Venue_ID, A2P.pid [Paper_ID], 
           P.abstract_research_area_category_id [Paper_Category_ID], 
           MD.Year
    FROM Merged_Data MD
    INNER JOIN sub_aminer_author2paper A2P 
    ON (MD.Author_ID = A2P.aid AND
        MD.Year      = A2P.p_year AND
        MD.Venue_ID  = A2P.p_venue_vid)
    INNER JOIN sub_aminer_paper P          
    ON (A2P.pid      = P.pid
        MD.Venue_ID  = P.p_venue_vid AND
        MD.Year      = P.p_year)
    WHERE MD.Author_ID = 677

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

As per the comment it solved the issue. So the same, I provide the answer for the question:

SELECT MD.Author_ID, MD.CoAuthor_ID, MD.Venue_ID, A2P.pid [Paper_ID], 
       P.abstract_research_area_category_id [Paper_Category_ID], 
       MD.Year
FROM Merged_Data MD
JOIN sub_aminer_author2paper A2P ON MD.Author_ID = A2P.aid AND
                                    MD.Year      = A2P.p_year AND
                                    MD.Venue_ID  = A2P.p_venue_vid
JOIN sub_aminer_paper P          ON MD.Venue_ID  = P.p_venue_vid AND
                                    MD.Year      = P.p_year
WHERE MD.Author_ID = 677 AND A2P.pid = P.pid

Adding the A2P.pid = P.pid in the WHERE clause will solve the problem.

Upvotes: 1

Related Questions