Skip to main content
added 254 characters in body
Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

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.

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 ;

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.

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.

Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

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 ;

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.