Left Outer Join Help

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 Solved
1 2 363
1 ACCEPTED SOLUTION

Dawid
Participant V

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%'

View solution in original post

2 REPLIES 2

Dawid
Participant V

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.

Top Labels in this Space
Top Solution Authors