distal-attribute
distal-attribute
distal-attribute
distal-attribute

mutiple joins on same table

User, date Message
Written by rosswholland
6 years ago
Category: General
3 posts since Fri, 04 Jul 08
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!
Written by ansgar
6 years ago
4986 posts since Fri, 07 Apr 06
Looks like a complex problem you should attack with some application routine rather than SQL if you ask me.
Written by Dr. DaveMoney, Euro
6 years ago
105 posts since Sun, 27 Jan 08

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);
}

 

Please login to leave a reply, or register at first.