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
Solved! Go to Solution.
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%'
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%'
Thank you, David. That worked.