student:
ssession:
ssubject:
SQL query to join all 3 tables above:
SELECT student.name,ssession.sem,student.id,ssubject.grade,ssubject.total,
ssubject.exam,ssubject.cwtotal FROM student
INNER JOIN ssession
ON student.studentid = ssession.studentid
INNER JOIN ssubject
ON ssession.sessionid=ssubject.sessionid
WHERE ssession.session='JAN 2009'
AND ssubject.code='ACC1101';
produces this:
The basic syntax for multiple inner joins:
SELECT fields... FROM firstTable
INNER JOIN secondTable
ON firstTable.primaryKey=secondTable.foreignKey
INNER JOIN thirdTable
ON secondTable.primaryKey=thirdTable.foreignKey
WHERE conditions...
The SELECT fields... can be from any of the 3 tables being joined!
Note:
For Delete DO NOT use INNER JOIN.
You should use LEFT JOIN. Left join will delete the rows
even if table 2 and/or 3 has no corresponding row.
Inner join will fail to delete unless all three tables are
successfully joined.
