从表中获取NULL值

Question

I want to get null values from other table if it doesn't exist with this query

SELECT * FROM Tithing_P LEFT JOIN Tithing 
ON Tithing_P.Tithing_ID = Tithing.Tithing_ID
WHERE YEAR=2015 AND Dateregistered <=2015

and this only get the opposite of what I wanted.

I have two tables

Tithing
Tithing_ID | Dateregistered | Name |
    1      |   2014-01-01   |   J  |
    2      |   2013-01-01   |   D  |

Tithing.Tithing_ID {PK}
Tithing_P.Tithing_ID {FK} to Tithing.Tithing_ID
Tithing_P_ID {PK}

Tithing_P
Tithing_ID | Tithing_P_ID | Jan | Feb | Year |
     1     |       1      |  10 |  10 | 2014 |

My query is

SELECT * FROM Tithing_P LEFT JOIN Tithing 
ON Tithing_P.Tithing_ID = Tithing.Tithing_ID
WHERE YEAR=2015 AND Dateregistered <=2015

on my page tithing.php?year=2015

I want to show those name who registered earlier from 2015 and if they don't have datas I want Jan and Feb to be 0/NULL

this what I wanted my output to be:
Name |  Jan  |  Feb  |  YEAR  |
  J  |   0   |   0   |  2015  |
  D  |   0   |   0   |  2015  |

Answer 1

What you are looking for here is an anti-join, which will require you to reverse the order of your tables in the LEFT JOIN, or use a RIGHT JOIN instead, e.g.:

SELECT * FROM Tithing LEFT JOIN Tithing_P 
ON Tithing.Tithing_ID = Tithing_P.Tithing_ID
WHERE Dateregistered <= 2015 AND Tithing_P.Tithing_ID IS NULL

SELECT * FROM Tithing_P RIGHT JOIN Tithing 
ON Tithing_P.Tithing_ID = Tithing.Tithing_ID
WHERE Dateregistered <=2015 AND Tithing_P.Tithing_ID IS NULL

The purpose of an anti-join is to discover what records exist on one side of an outer join that do not have any matching records on the other side of the outer join.