two tables left join the one, not getting all the data from the join

Question

I have a tickets table as this:

tickets:
id       integer primary key
home_org varchar

There is a homeorgs table that holds all the home organizations and includes a division code as this:

homeorgs:
home_org      varchar
division_code varchar

I want to be able to show number of tickets per month per division, even if a particular division has not submitted any tickets. For the division that have no tickets, I need it to show 0 (zero).

Here is the sql:

select 
       count(t.id) as ticket_count,
       to_number(to_char(t.submitdate,'MM'), '99') as mon,
       to_number(to_char(t.submitdate,'YYYY'), '9999') as yr,
       nd.division_key 
  from homeorgs as h 
       LEFT JOIN tickets as t
           ON t.home_org = h.home_org 
           and t.submitdate >= '2018-02-01 00:00:00'
           and t.submitdate <= '2018-02-28 23:59:59'
  where t.home_org is not null
 group by h.division_key, mon, yr
 order by yr, mon, h.division_key

This sql does not bring in the homeorg rows in which no tickets have been submitted.

What am I doing wrong here?

Please remove the where condition with "where t.home_org is not null" — Joshan George, Mar 07 '18 at 15:27
Hi. Find out what left join returns: inner join rows plus unmatched left table rows extended by nulls. Your where removes any unmatched rows added by left over inner, so you can't both want a left over an inner *and* want that where. This is a duplicate, and it is a faq because people do not try hard enough to google. Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. — philipxy, Mar 07 '18 at 20:40
Possible duplicate of [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, Mar 07 '18 at 20:41

score 0 · Answer 1 · answered Mar 07 '18 at 15:30

Just remove the " t.home_org is not null" and this is preventing the unmatched records from the ticket table.

  select 
       count(t.id) as ticket_count,
       to_number(to_char(t.submitdate,'MM'), '99') as mon,
       to_number(to_char(t.submitdate,'YYYY'), '9999') as yr,
       nd.division_key 
  from homeorgs as h 
       LEFT JOIN tickets as t
           ON t.home_org = h.home_org 
           and t.submitdate >= '2018-02-01 00:00:00'
           and t.submitdate <= '2018-02-28 23:59:59'
 group by h.division_key, mon, yr
 order by yr, mon, h.division_key

score 0 · Answer 2 · answered Mar 07 '18 at 15:35

Your problem is probably just the where clause. This is to suggest simpler logic for other parts of the query:

select count(t.id) as ticket_count,
       extract(month from t.submitdate) as mon,
       extract(year from t.submitdate) as yr,
       h.division_key 
from homeorgs h left join
     tickets as t
    on t.home_org = h.home_org and
       t.submitdate >= '2018-02-01' and
       t.submitdate < '2018-03-01'
group by h.division_key, mon, yr
order by yr, mon, h.division_key ;

Personally, I wouldn't split the year and month into different columns. I would just truncate the date to the beginning of the month:

select count(t.id) as ticket_count, date_trunc('month', t.submitdate) as yyyymm,
       h.division_key 
from homeorgs h left join
     tickets as t
    on t.home_org = h.home_org and
       t.submitdate >= '2018-02-01' and
       t.submitdate < '2018-03-01'
group by h.division_key, yyyymm
order by yyyymm, h.division_key ;

two tables left join the one, not getting all the data from the join

2 Answers2