homepage Welcome to WebmasterWorld Guest from 23.23.57.144
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Mysql query, is this possible?
Do I need to use joins
Pollos

5+ Year Member



 
Msg#: 4386692 posted 1:40 pm on Nov 14, 2011 (gmt 0)

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



 
Msg#: 4386692 posted 3:18 pm on Nov 14, 2011 (gmt 0)

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

5+ Year Member



 
Msg#: 4386692 posted 5:12 pm on Nov 14, 2011 (gmt 0)

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

5+ Year Member



 
Msg#: 4386692 posted 8:55 pm on Nov 14, 2011 (gmt 0)

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



 
Msg#: 4386692 posted 3:42 pm on Nov 15, 2011 (gmt 0)

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

5+ Year Member



 
Msg#: 4386692 posted 8:02 pm on Nov 17, 2011 (gmt 0)

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



 
Msg#: 4386692 posted 8:37 pm on Nov 17, 2011 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved