How to join two tables by multiple columns in SQL?

Question

I have two tables named Evaluation and Value.

In both tables, there are four columns. But three of the four are the same. In other words, they both have the CaseNum, FileNum, ActivityNum columns. In addition to those, the Evaluation table has the Grade column, and the Value table has the Score column.

I want to merge the two into one table, joining by CaseNum, FileNum, and ActivityNum, so I have a new table of five columns, including Value and Score.

Can I use INNER JOIN multiple times to do this?

Answer 1

Yes: You can use Inner Join to join on multiple columns.

SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E INNER JOIN Value V ON E.CaseNum = V.CaseNum AND     E.FileNum = V.FileNum AND      E.ActivityNum = V.ActivityNum 

Create table

CREATE TABLE MyNewTab(CaseNum int, FileNum int,     ActivityNum int, Grade int, Score varchar(100)) 

Insert values

INSERT INTO MyNewTab Values(CaseNum, FileNum, ActivityNum, Grade, Score) SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E INNER JOIN Value V ON E.CaseNum = V.CaseNum AND     E.FileNum = V.FileNum AND      E.ActivityNum = V.ActivityNum 

Answer 2

No, just include the different fields in the "ON" clause of 1 inner join statement:

SELECT * from Evalulation e JOIN Value v ON e.CaseNum = v.CaseNum
    AND e.FileNum = v.FileNum AND e.ActivityNum = v.ActivityNum