mysql - join one row to all row and returning all row

Question

can I get data like this from my table

| id_outlet| date       |  count(msisdn) |  
| 34.10.1  |  2014-08   |      0         |
| 34.10.1  |  2014-09   |      3         |
| 34.10.1  |  2014-10   |      2         |
| 34.10.2  |  2014-08   |      1         |
| 34.10.2  |  2014-09   |      0         |
| 34.10.2  |  2014-10   |      0         |  

So I have 2 tables
1. table outlet (unique)
2. table sales (detail of table outlet)
As u see in my second table there are 3 periode (2014-08, 2014-09, 2014-10)
I want join that periode with id_outlet in first table like that example.
Can I?
Please Help me

Answer 1

Using a CROSS JOIN:-

SELECT
    o.id_outlet,
    s_main.periode,
    o.branch, 
    count(msisdn)
FROM
(
    SELECT DISTINCT SUBSTRING(date,1,7) AS periode
    FROM sales
) s_main 
CROSS JOIN outlet o
LEFT OUTER JOIN sales s
ON s_main.periode = SUBSTRING(s.date,1,7)
AND o.id_outlet = s.id_outlet
WHERE (o.STATUS LIKE 'STREET%')
GROUP BY s_main.periode, o.branch, o.id_outlet

If you have a table of dates then you can just use that rather than the sub query to get the dates (which also avoids the potential problem of not having a date in the results for a month where there has been zero sales for any outlet).