Return all rows from table1 and corresponding rows from table2 even if null

Question

I'm new into SQL so please bear with me. The question itself is confusing as I don't really know how to ask it. Thing is, I have two tables, table1 lists all printers. Table2 lists their page counters, with year/month parameter. What I want to show is ALL printers with their respective page counters filtered by year/month, and NULL if there is no page counter for that specific printer on that specific time frame. To try and clarify a little, this is what I have:

Table1:

id

model

location

Table2:

id

printerid

counter

year

month

SELECT
   tb1.id,
   tb1.model,
   tb1.location,
   tb2.counter
FROM
   table1 tb1 LEFT JOIN table2 tb2 ON tb1.id = tb2.printerid
WHERE
   tb2.year = 2017 AND tb2.month = 1

I know the WHERE clause is messing things up, and this query only returns printers that have counters on that year/month parameter. If I remove the WHERE from the query I get duplicates (if a printer has (and will have) counters on different year/months).
What I want is something like this:

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1
printer3     null       null      null

Instead I'm getting this (without WHERE clause):

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1
printer3     null       null      null
printer1     150        2017      2

Or this (with WHERE clause):

Printer      Pages      Year      Month
printer1     100        2017      1
printer2     200        2017      1

Any help would be much appreciated

Answer 1

You only need to move the WHERE conditions (that involve table2) to the joining, ON condition:

SELECT
   tb1.id,
   tb1.model,
   tb1.location,
   tb2.counter
FROM
   table1 tb1 LEFT JOIN table2 tb2 
   ON (  tb1.id = tb2.printerid
     AND tb2.year = 2017 
     AND tb2.month = 1 
      ) ;

The ON condition can be as complex as required, it doesn't necessarily be a simple equality, although that's the most common (the parentheses around the condition are not required either, just to make the code a bit more clear).

---

It's unclear how you get Printer, Pages, Year, Month columns in the result when you have id, model, location, counter in the SELECT list.