mysql - 显示来自另一个表的关键记录的所有更改

Question

给定这两个表

subject               subject_changes
-----------            ------------------
id                     id   
subjectName           version           
subjectCode           subjectCode
version               previousSubjectCode

如何显示 subjectCode 中的所有更改？我必须根据以前的SubjectCode 引用subjectCode 并获取该版本的相关数据。

在主题表中，我有

1, 'Animals','Q001', 1
2, 'Animal Kingdom', 'Q012',2 
2, 'Animal Kingdom', 'Q023',3 

并在 subject_changes 中

1, 2, 'Q012', 'Q001'
2, 3, 'Q023', 'Q012'

伪代码

SELECT * FROM subject WHERE subjectCode= ?

SELECT * FROM subject_changes WHERE subjectCode=?

foreach subjectCode 
   get previousSubjectCode
   get subjectName from subject table

SELECT * FROM subject WHERE subjectCode = subject_changes.previousSubjectCode 

结果表应该是这样的

version   subjectName     subjectCode
-------   -----------      ---------- 
version 3 Animal Kingdom  Q023  
version 2 Animal Kingdom  Q012
version 1 Animal          Q001

Answer 1

SELECT
    subject.version, subject.subjectName, subject.subjectCode
FROM subject
INNER JOIN subject_changes USING (subjectCode)
WHERE subjectCode = ?