0

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

  • 1
    • Although this could be another place to use the ubiquitous (and very useful) tally table construct, there is an easier way to do this in the WHERE clause, e.g. WHERE ISNULL(tb2.year, 2017) = 2017 AND ISNULL(tb2.month, 1) = 1 – Laughing Vergil Jan 10 '17 at 18:00
    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.

    2
    • Yeah, the results clearly don't match the query, I was really confused. Sorry. Your code works like a charm! Didn't know I could use ANDs on the JOINs. Thanks so much! I owe you a beer, or perhaps a coffee. – Marcelo Fialho Jan 10 '17 at 18:20
    • 1
      You can use any condition, however complex, not a single equality, although it's the most common. Adding parentheses to show more clearly what happens. – ypercubeᵀᴹ Jan 10 '17 at 18:39

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.