Sql-server – Return all rows from table1 and corresponding rows from table2 even if null

sql server

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

  • Best Answer

    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.