I have put together what seems to be a fairly simple left outer join query but it is acting like a full query.
I am looking for all locations from A and the count of them from B. I never get a count of 0 however. Not all locations have the matching “watchdog” messages so I would expect to see some 0 counts in there.
Any ideas?
Thanks in advance for your time.
Bob
select a.location
,coalesce(count(b.message),0) as Watchdog_Count
from events_pr.thdks_app_logs a
left outer join events_pr.thdks_app_logs b
on a.location = b.location
where
b.message like '%msg="Received alert" alert=Watchdog%'
group by 1
order by 2,1
limit 2500
Best answer by Dawid_Nawrot
Because you used b.message in WHERE clause, it technically changes it to an INNER JOIN.
Try to move the WHERE clause to the join:
select a.location
,coalesce(count(b.message),0) as Watchdog_Count
from events_pr.thdks_app_logs a
left outer join events_pr.thdks_app_logs b
on a.location = b.location AND b.message like '%msg="Received alert" alert=Watchdog%'