Getting the new records from changes table when referring to one value with union and subquery

Go To StackoverFlow.com

0

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.

2012-04-03 21:12
by Finally Forever


0

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
2012-04-03 21:28
by joshuahedlund
This is definitely the best method. The tables were converted from excel, but I will give it a shot. Thank you - Finally Forever 2012-04-03 21:39
Ads