Select a row that doesn't exist in t2, based o

问题:

I'm using MySQL and PHP. I have 2 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. There's only 3 entries because client #36 stopped being a client in January.

This table is called analytics

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

I want to join(?) union(?) these tables and get an output that looks like this: (NB, the null value)

+------------------------------------------------+
|  month      year      client      value        |
+------------------------------------------------+
|   10         2015       36      "Some value"   |
|                                                |
|   11         2015       36     "Another value" |
|                                                |
|   12         2015       36      "third value"  |
|                                                |
|   1          2016       36         NULL        |
+------------------------------------------------+

I tried the query below, among a myriad of other things but always either returns nothing or just 3 rows

SELECT *
FROM analytics a
WHERE a.clientfk = 36
AND NOT EXISTS (SELECT null FROM dates d
                WHERE d.id = a.datefk)

回答1:

you are looking for LEFT JOIN

SELECT `dates`.`month`, `dates`.`year`, `analytics`.`clientfk` AS 'client', `analytics`.`value`
    FROM `dates`
        LEFT JOIN `analytics` ON `analytics`.`datefk` = `dates`.`id` AND `analytics`.`clientfk` = '36'
    WHERE 1

回答2:

You will get result by LEFT JOIN.

SELECT 
d.month as month, d.year as year, a.clientfk as client, a.value as value 
from dates d
LEFT JOIN analytics a on d.id = a.datefk 
WHERE a.clientfk = 36

By this you will get Client also null. Because you don't have data in analytics for date id 4.

+------------------------------------------------+
|  month      year      client      value        |
+------------------------------------------------+
|   10         2015       36      "Some value"   |
|                                                |
|   11         2015       36     "Another value" |
|                                                |
|   12         2015       36      "third value"  |
|                                                |
|   1          2016       NULL         NULL      |
+------------------------------------------------+

But if you still want client id to be present in that column you can use case.

SELECT d.month as month, d.year as year, CASE 
          WHEN a.clientfk = null THEN 36
          ELSE a.clientfk 
         END as client, a.value as value
from 
dates d
LEFT JOIN analytics a on d.id = a.datefk 
WHERE a.clientfk = 36