Date: 12/04/07 (MySQL Communtiy)    Keywords: no keywords

I'm writing a script to display Widgets and Values.  There are two tables in play here.  The first is my WidgetLookup table.  It contains widget_id and widget_description.   The second, WidgetSales, contains customer orders, with a widget_id column among other things. 

The report needs to display *all* Widgets from the WidgetLookup table, with a corresponding count of total Widgets sold and unique customers per widget. 

So something like this works perfect as long as every Widget is represented in the WidgetSales table in the supplied date range.

            SELECT
                COUNT(WidgetSales.customer_id) AS total_customers,
                COUNT(WidgetSales.widget_id) AS total_widgets,
                WidgetLookup.widget_description
            FROM
                WidgetSales
                LEFT JOIN WidgetLookup ON WidgetLookup.widget_id = WidgetSales.widget_id
            WHERE
                WidgetSales.sale_date = '2007-12-24'
            GROUP BY
                WidgetSales.widget_id

The problem is, not every Widget is sold in the given date range.  So this query will only display rows for items sold.  I need to display -all- Widgets, with "0" displayed on rows that didn't have sales.  Sounds easy but I can't figure out how to structure my query.

Any help or pointers are greatly appreciated.   I've stripped down my actual example, so if I need to post more detail please let me know. 

Source: http://community.livejournal.com/mysql/122012.html