Return rows with NULL when there's no match in another table

Question

I'm using MySQL and PHP. I have 4 tables.

The first one has dates in it. This table is called dates

+-------+-------------+----------+---------+
|  id   |  unixTime   |  month   |  year   |
+------------------------------------------+
|  1    | 1443657600  |    10    |   2015  |
|       |             |          |         |
|  2    | 1443657600  |    11    |   2015  |
|       |             |          |         |
|  3    | 1443657600  |    12    |   2015  |
|       |             |          |         |
|  4    | 1443657600  |    01    |   2016  |
|       |             |          |         |
+-------+-------------+----------+---------+

The table below will show if you query SELECT * FROM analytics WHERE clientfk=36 AND metricfk=3. There's only 3 entries because client #36 stopped being a client in January.

This table is called analytics

+----------------------------------------------------------+
|  id       datefk          value       clientfk   metricfk|
+----------------------------------------------------------+
|  156        1          "some value"       36         3   |
|                                                          |
|  157        2        "another value"      36         3   |
|                                                          |
|  157        3         "thrid value"       36         3   |
|                                                          |
+----------------------------------------------------------+

I have another table called metrics that looks like this. It is a list of metrics and will be used to bring up all the values from analytics for all dates. The last table is just clients

+--------------------------------+  
|  id         metric             |
+--------------------------------+
|   1       "Some metric"        |
|                                |
|   2      "Another metric"      |
|                                |
|   3      "A third metric"      |
|                                |
|   4      "One more metric"     |
+--------------------------------+

+--------------------------------+  
|  id         clientName         |
+--------------------------------+
|   1       "Some client"        |
|                                |
|   2      "Another client"      |
|                                |
|   3      "A third client"      |
|                                |
|   4      "One more client"     |
+--------------------------------+

I need my output to be a combination of those tables based on a metric, but include rows where there is no data. For example client #36 stopped being a client in January, so I want a NULL row. Like this:

+------------------------------------------------+
|  month      year      client      value        |
+------------------------------------------------+
|   10         2015       36      "Some value"   |
|                                                |
|   11         2015       36     "Another value" |
|                                                |
|   12         2015       36      "third value"  |
|                                                |
|   1          2016       36         NULL        |<-- this row has NULL
|                                                |
|   10         2015       37      "value 1"      |
|                                                |
|   11         2015       37      "value 2"      |
|                                                |
|   12         2015       37       "value"       |
|                                                |
|   1          2016       37   "One more value"  |
+------------------------------------------------+

I've tried many things, JOIN, LEFT JOIN, COALESCE, and asking the wrong question, but my knowledge of MySQL is usually limited to SELECT * FROM table

Answer 1

Try LEFT OUTER JOIN. More info here: http://dev.mysql.com/doc/refman/5.7/en/join.html

EDIT try this sql:

Select * from dates
LEFT OUTER JOIN analytics on analytics.datefk = dates.id
LEFT OUTER JOIN clients on analytics.clientfk = client.id