mysql – How to join two tables by multiple columns in SQL?

mysql – How to join two tables by multiple columns in SQL?

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

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

mysql – How to join two tables by multiple columns in SQL?

You should only need to do a single join:

SELECT e.Grade, v.Score, e.CaseNum, e.FileNum, e.ActivityNum
FROM Evaluation e
INNER JOIN Value v ON e.CaseNum = v.CaseNum AND e.FileNum = v.FileNum AND e.ActivityNum = v.ActivityNum

Leave a Reply

Your email address will not be published.