根据lastmodified column比较两个表？

Question

I am using this query to check tickets group by month and year

SELECT 
    MONTH((lastmodified)) as month, 
    YEAR((lastmodified)) as year,
    COUNT(num_of_tickets) as tickets
    FROM visitors_2013
    GROUP BY 
    MONTH((lastmodified)), 
    YEAR((lastmodified))

this return something like

Array
(
    [0] => Array
        (
            [month] => 1
            [year] => 2013
            [tickets] => 1521
        )

    [1] => Array
        (
            [month] => 2
            [year] => 2013
            [tickets] => 513
        )

    [2] => Array
        (
            [month] => 12
            [year] => 2012
            [tickets] => 146
        )

)

I have put this in a php function so i can get the data per year (data is stored in a seperate table per year). I call function get_ticket_per_year($year) twice for 2013 and 2014

Array
(
    [0] => Array
        (
            [month] => 1
            [year] => 2013
            [tickets] => 1521
        )

    [1] => Array
        (
            [month] => 2
            [year] => 2013
            [tickets] => 513
        )

    [2] => Array
        (
            [month] => 12
            [year] => 2012
            [tickets] => 146
        )

)

Array
(
    [0] => Array
        (
            [month] => 12
            [year] => 2013
            [tickets] => 26
        )

)

And i like to compare month-year-total tickets from another table (visitors_2014) by plotting the data per month per year in a graph. For this i am using the morris js library. This is the outcome i need

data: [ {
            m: 'dec',
            2012: 146,
            2013: 26
        }, {
            m: 'jan',
            2013: 1521,
            2014: 
        }],

So how can i combine the mysql query so it's one query

SELECT 
prevTbl.MONTH((lastmodified)) as prev_month, 
prevTbl.YEAR((lastmodified)) as prev_year,
prevTbl.COUNT(num_of_tickets) as prev_tickets,
currTbl.MONTH((lastmodified)) as curr_month, 
currTbl.YEAR((lastmodified)) as curr_year,
currTbl.COUNT(num_of_tickets) as curr_tickets
FROM visitors_2013 prevTbl, visitors_2014 currTbl 
GROUP BY 
MONTH((lastmodified)), 
YEAR((lastmodified))

Answer 1

Join your queries:

SELECT * FROM ( [your first query here] ) AS data1
LEFT JOIN ( [your second query here] ) AS data2
ON [your relation between the two query here]

In your case (not tested):

SELECT * FROM (

    SELECT
        `num_of_tickets` AS `tickets2013`,
        MONTH(`lastmodified`) AS `month`
    FROM `visitors_2013`
    GROUP BY 
        MONTH(`lastmodified`)

) AS `data2013`
LEFT JOIN (

    SELECT
        `num_of_tickets` AS `tickets2014`,
        MONTH(`lastmodified`) AS `month`
    FROM `visitors_2014`
    GROUP BY 
        MONTH(`lastmodified`)

) AS `data2014`
ON (
    `data2013`.`month`=`data2014`.`month`
)

Why do not you store your (actively used) data in the same table (much more simpler)?