How can i get a my SQL count function to return 0?

I'm fairly new to SQL so excuse my ignorance or if I'm asking a basic question here. What I'm trying to do is get my table to return 0 values if an outlet didn't create an assignment in the last day. The code I'm using is this:

    SELECT public.outlets.title AS "Outlet",
           COUNT(public.outlets.id) AS "Assignment Count"
    FROM public.assignments 
    JOIN public.users
      ON public.users.id=public.assignments.creator_id
    LEFT JOIN public.outlets
      ON public.outlets.id = public.users.outlet_id
    WHERE public.outlets.dispatch_enabled = 'True' 
      AND DATE(public.assignments.created_at) = DATE(CURRENT_TIMESTAMP- interval '1 day') 
    GROUP BY public.outlets.title
    HAVING count(public.outlets.id) = 0
    ORDER BY public.outlets.title ASC

I've tried COALESCE but to be honest I don't really understand it well enough to know if I'm implementing it correctly. As it stands, the current code i'm using is giving me "NO ROWS RETURNED". I've tried taking various parts of my where clause out to see if that will help but nothing. Any help will be greatly appreciated!!

Solution

Are you looking for Outlets with a count of Assignments? Because then I think you want outlets to be the "FROM" and a left join on assignments instead of the other way around.

   SELECT public.outlets.title AS "Outlet",
       COUNT(public.assignments.id) AS "Assignment Count"
FROM public.outlets
JOIN public.users
  ON public.outlets.id = public.users.outlet_id   
LEFT JOIN public.assignments
  ON public.users.id=public.assignments.creator_id
  AND DATE(public.assignments.created_at) = DATE(CURRENT_TIMESTAMP-interval '1 day') 
WHERE public.outlets.dispatch_enabled = 'True' 
GROUP BY public.outlets.title
HAVING count(public.assignments.id) = 0
ORDER BY public.outlets.title ASC