I have 2 table which stores the course and the changes to a course name throughout the years
table courses
year, record
2009, 'Basic English 101'
2009, 'Maths 100'
2010, 'Introduction to English 102'
2010, 'Maths 101'
2010, 'History 100'
2011, 'English 103'
2012, 'Maths 102'
table changes
year, oldrecord', 'newrecord'
2010, 'Basic English 101', 'Introduction to English 102'
2010, 'Maths 100', 'Maths 101'
2011, 'Introduction to English 102', 'English 103'
2012, 'English 103', 'English 104'
2012, 'Maths 101' 'Maths 102'
What I want is to display the coursename throughout the years,
With this query I manage to get the next record for English 101 in 2009 & 2010
SELECT year, record AS coursename FROM courses
WHERE record= 'Basic English 101'
UNION
SELECT Year, newrecord FROM changes
WHERE newrecord = (SELECT newrecord FROM changes WHERE oldrecord='Basic English 101')
The result
year, coursename
2009, 'Basic English 101'
2010, 'Introduction to English 102'
My question is how do I get the rest of the record for the other years based on the value of the new record?
I can do another subquery but if the courses split into 2 I will get the subquery returns more than one row error.
Thanks.
You need to add a unique id field to both tables that will link the courses without relying on the names. Make this an integer with an autoincrement primary key.
Table Courses should only store the current names
year, record_id, record,
2010, 1, 'History 100'
2011, 2, 'English 103'
2012, 3, 'Maths 102'
Table changes should reference the related record_id
year, record_id, oldrecord, newrecord
2010, 2, 'Basic English 101', 'Introduction to English 102'
2010, 3, 'Maths 100', 'Maths 101'
2011, 2, 'Introduction to English 102', 'English 103'
2012, 2, 'English 103', 'English 104'
2012, 3, 'Maths 101' 'Maths 102'
Then you can get a full history of, say, the English record by doing something like
SELECT oldrecord FROM changes WHERE record_id = 2