Join subquery by year and month

I want to collect data from differed tables grouped by year and month.
I'm using subquery for each sub data.
Using mysql.

SELECT YEAR(td.db_date) AS stat_year, job.total_jobs
    FROM time_dimension AS td

    LEFT JOIN (

        SELECT YEAR(j.published_at) AS stat_year, COUNT(*) AS total_jobs
        FROM jobs AS j
        GROUP BY stat_year

    ) AS job ON job.stat_year = stat_year

    GROUP BY stat_year

When I'm joining data with id - it works perfect. But with this dates...

For time_dimenstion I'm using this https://gist.github.com/bryhal/4129042

Subquery return this data

2014 | 2334
2015 | 2000

But whole query starts with the first year and return same data but wrong matched with year

1 | 2334
2 | 2000

There is one problem with aliases usability in different parts of a query - aliases defined in the SELECT clause are only available in GROUP BY, ORDER BY and HAVING, not in WHERE or JOIN .. ON, so as you do not add the table specificator to the second stat_year, it probably takes the same column and the = is trivially true.

You can check that with EXPLAIN EXTENDED ; SHOW WARNINGS; which will tell you how MySQL "sees" your query (canonizing all column references with db and table names).

You need to replace it by the actual expression - ON job.stat_year = YEAR(td.db_date) - the one in the GROUP BY can stay the same as they will be equivalent after the join is done.

StackExchange Database Administrators Q#119076, answer score: 2