mutiple joins on same table

Hi all. Using Heidi on MySQL. Table has following general structure:

Year, Month, text - note 1 record per month per year over last 60 years, but not EVERY month has a record (generally 9 out of 12 months)

I'm trying to achieve slection of the text from the same month as current month, or the nearest to it in an earlier month, but only for the years 10, 20, 30, 40, 50 and 60 years ago. I can achieve some but not together as follows:

select distinct Year from table where mod(table.year, 10) = mod(year(current_date), 10) and table.year <> year(current_date)

This gives me the years I want fine. Within each year I want the closest to current month - I can get it for one year (in this example, last year):

select * from table where table.year = year(current_date) - 1
and table.month <= month(current_date) order by table.month desc limit 1

So how do I get each of the years in the first select to give me the record in the second select for the particular year? I have tried joining them left and right both ways (2nd select 1st and 1st select 1st) but am getting no joy.. Can anyone out there help?

Any help gratefully received as I am going round in circles here! Just for fun, once I've got that one sorted, I've got to join the result to another table, but I've already done that successfully on 1 record so I am anticipating no trouble on that one!

Looks like a complex problem you should attack with some application routine rather than SQL if you ask me.

void someFunction()
{
MySQL my;
char szQueryText[1024], szQueryText2[1024];
MYSQL_RES *My_result = NULL, *My_result2 = NULL;
int theYear = 0, theMonth = 0;
snprintf(szQueryText, sizeof(szQueryText), "select distinct Year, Month from table where mod(table.year, 10) = mod(year(current_date), 10) and table.year <> year(current_date)");
My_result = MySQL__query(szQueryText);
int num = my.Query(szQueryText);
while (num > 0)
{
my.NextRow();
num--;
theYear = atoi(my.GetData("Year"));
theMonth = atoi(my.GetData("Month"));
snprintf(szQueryText2, sizeof(szQueryText2), "select * from table where table.year = %d - 1 and table.month <= %d order by table.month desc limit 1", theYear, theMonth);
My_result2 = MySQL__query(szQueryText2);
}
MySQL__endquery(My_result);
MySQL__endquery(My_result2);
}