Solved

Left Outer Join Help

  • 21 January 2021
  • 2 replies
  • 83 views

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

 

icon

Best answer by Dawid 21 January 2021, 08:27

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 original

This topic has been closed for comments

2 replies

Userlevel 6
Badge +1

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.