SQL Question actually. Select items for all 12 months

Are you sure your query is even running?  You are requesting a field and a sum, but you don't have a group by clause.

I would start from something this

SELECT monthsInYear.month, IFNULL(income.amount, 0 ) AS amount

FROM monthsInYear

WHERE EXTRACT(MONTH FROM income.dateAdded) = monthsInYear.monthID

AND income.orgID = 10031

AND YEAR(income.DateAdded) = '2011'

ORDER BY EXTRACT(MONTH FROM monthsInYear.month) ASC

Use a left join.

Do a left join the way you did in the opening post.  In other words, the table from which you want all the rows should be left of the word left.

Hi

As Dan Baruk rightly said try something like below

SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount

FROM monthsInYear m

LEFT JOIN getExpenses e ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)

WHERE e.orgID = 10031

AND YEAR(e.dateAdded) = '2011'

GROUP BY m.month, m.monthID

ORDER BY EXTRACT(MONTH FROM e.dateAdded) ASC

Here is a got article related to joins
http://www.devx.com/dbzone/Article/17403/1954

Regards

Sreekar

It might be your grouping fields.  Group by the non-aggregate fields in your select clause.  Once again, I'm surprised the query actually ran the way you presented it.

Sreeindia wrote:

 

As Dan Baruk rightly said try something like below

 

SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount

FROM monthsInYear m

LEFT JOIN getExpenses e ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)

WHERE e.orgID = 10031

AND YEAR(e.dateAdded) = '2011'

GROUP BY m.month, m.monthID

ORDER BY EXTRACT(MONTH FROM e.dateAdded) ASC

 

I think it is sufficient to group by just one column, using the table to the left of the join, like this:

SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount

FROM monthsInYear m

LEFT JOIN getExpenses e ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)

WHERE e.orgID = 10031

AND YEAR(e.dateAdded) = '2011'

GROUP BY m.monthID

ORDER BY m.monthID ASC

But when I run this, it gives me total for only the months that have a total and not the 0 for the ones that come up as null. Any ideas?

.... 

Also tried

 

SELECT m.month, IFNULL( SUM(i.cost), 0 ) AS amount

FROM getExpenses i LEFT OUTER JOIN monthsInYear m

ON m.monthID = EXTRACT(MONTH FROM i.dateAdded)

WHERE orgID = 10031

AND YEAR(i.dateAdded) = '2011'

GROUP BY m.month

ORDER BY EXTRACT(MONTH FROM i.dateAdded) ASC

There are two problems. First, the tables in your join are reversed. A LEFT JOIN returns all records from the left hand table (expenses) even if there are no matching records in the right hand table. What you want is the opposite. So the months table must on be on the left side of the JOIN:

            ...

            FROM    monthsInYear m LEFT JOIN getExpenses i ON m.monthID = EXTRACT(MONTH FROM i.dateAdded)

Second, when there is no matching expense record, the column values will all be null. So as soon as you use one of the expense fields in your WHERE clause, ie

            WHERE i.orgID = 10031

            AND      YEAR(i.dateAdded) = '2011'

... those missing records are dropped, because a null value is never equal to anything. So you are negating the outer join altogether. You need to move those conditions into the JOIN clause instead: 

              SELECT m.month,  IFNULL( SUM(i.cost), 0 ) AS amount

              FROM   monthsInyear m LEFT OUTER JOIN getExpenses i

                                        ON m.monthID = EXTRACT(MONTH FROM i.dateAdded)

                                        AND i.orgID = 10031

                                        AND YEAR(i.dateAdded) = 2011

             GROUP BY m.month

             ORDER BY m.month