Mysql query, is this possible?

Do I need to use joins

[Pollos]

I have two tables, called 2011 and 2012 both are set up the same I.E.

ID -- date -- cost

Each table has:-

ID = 1 to 365,
date 01/01/year to 31/12/year [in PHP strtotime format]
cost has the 'cost' [int]

Is it possible to select all the 'costs' between two dates where the dates span the two tables.

Example SELECT cost FROM table2011, table2012 WHERE date is BETWEEN 20/12/2011 and 10/01/2012 [dates in strtotime format]

I would need the 'costs' from the last X number of records from the 2011 table and the first X number of records from the 2012 table.

Is this possible? do I use one of the JOINS, which I'm reading up on but a little confused so far.

I'm using Mysql 5

Any help appreciated thanks.

[Jstanfield]

Does UNION get you what you want?

SELECT cost FROM table2011 WHERE date is BETWEEN 20/12/2011 and 10/01/2012
UNION
SELECT cost FROM table2012 WHERE date is BETWEEN 20/12/2011 and 10/01/2012

[Pollos]

I've tried various 'JOINS' with no results although I don't know if I have the syntax right.

I'll give UNION a try

Thanks.

[Pollos]

SELECT cost FROM table2011 WHERE date BETWEEN date1 AND date2
UNION ALL
SELECT cost FROM table2012 WHERE date BETWEEN date1 AND date2

Works great and gives the correct results. Using the last two days of 2011 and the first two days from 2012, gives:-
15.00
15.00
10.00
10.00
Which is correct, but if I use

$query_test3 ="
SELECT *, SUM(cost) FROM table2011 WHERE date BETWEEN date1 AND date2
UNION ALL
SELECT *, SUM(cost) FROM table2012 WHERE date BETWEEN date1 AND date2
";

The result is way off, any ideas why

[Jstanfield]

if you select * and sum(cost)

you'll get a weird result. not sure what it'll do.

what is it you are looking for? sum(cost) grouped by date?

SELECT date, SUM(cost) FROM table2011 WHERE date BETWEEN date1 AND date2 GROUP BY date
UNION ALL
SELECT date, SUM(cost) FROM table2012 WHERE date BETWEEN date1 AND date2 GROUP BY date

[Pollos]

SELECT SUM(tbl.cost) as total
FROM tbl.table2011
WHERE date BETWEEN date1 AND date2

Gives me the tbl.cost total between the dates, great just what I want, if the dates are in the same year, thus in the same table.

At the moment to get the total of tbl.cost when date1 is in tbl.2011 and date2 is in tbl.2012, thus looking in two tables, I'm doing two selects:-

#Check if dates are in same year,
#If true do SELECT above,
#If false, do both SELECTS below

SELECT SUM(tbl.cost) as total
FROM tbl.table2011
WHERE date1 >=date1

And

SELECT SUM(tbl.cost) as total
FROM tbl.table2012
WHERE date <=date2

Then adding the two.

What I'd like is to do have one SELECT statement which covers all eventuality's I.E.

Both dates are in this year
Both dates are in next year
Date1 is in this year and Date2 is in next year.

Is this possible.

Cheers

[Jstanfield]

nest them... this should get you what you want, whether or not date1 and date2 are different years:

select SUM(both.cost) from ( 
 SELECT cost FROM table2011 WHERE date BETWEEN date1 AND date2 
 UNION ALL 
 SELECT cost FROM table2012 WHERE date BETWEEN date1 AND date2 
) both 

Around 1/1/2013, you'll have to come back and change this. Depending on what you're using (Ruby,PHP,C#,other), you can figure out the year of date1 and the year of date2, and dynamically build the SQL statement in a loop for all years between date1 and date2. So that you end up with:

select SUM(a_bunch.cost) from ( 
 SELECT cost FROM table2011 WHERE date BETWEEN date1 AND date2 
 UNION ALL 
 SELECT cost FROM table2012 WHERE date BETWEEN date1 AND date2 
 UNION ALL 
 SELECT cost FROM table2013 WHERE date BETWEEN date1 AND date2 
 UNION ALL 
 SELECT cost FROM table2014 WHERE date BETWEEN date1 AND date2 
) a_bunch