How to join two searches on a common field and include instances that don't overlap

[Traer001]

Hello,

I have one search where I am finding inventory details for items going into carts and another search for cart status. The two searches have one common field (named differently in each) for the cart location and I am trying to join them to show how many inventory items have been put into each cart and when the last item was put into the cart.

Both searches have the same index, host and sourcetype.

My search currently looks like this. It's in the works, but I'm stuck. I want it to also show carts that have not had any inventory put into it yet and count the items for each cart (even if it's zero). How can I rewrite this to show the info I want?

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sbegin"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sselect\snew\ssection:\s(?<section>\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sset\scurrent\slocation:\s(?<section_name>\w+-\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\snext\slocation\sis:(?<section_name>\w+-\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sset\scurrent\slocation:\s\w+-(?<section_number>\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\snext\slocation\sis:\w+-(?<section_number>\d+)"
| eval select_time=if(like(_raw, "%select new section%"), _time, null)
| eval selected_at=strftime(select_time, "%Y-%m-%d %H:%M:%S")
| eval start_time=if(like(_raw, "%begin%"), _time, null)
| eval set_time=if(like(_raw, "%set current location%"), _time, null)
| eval next_loc_time=if(like(_raw, "%next location%"), _time, null)
| where isnotnull(cart_id) and ((like(_raw, "%set current location%") and like(section_name, "AA%")) or like(_raw, "%begin%") or (like(_raw, "%select new section%")))
| eventstats latest(section) as section_id latest(section_name) as section_name latest(section_number) as section_number latest(selected_at) as selected_at by cart_id
| where isnotnull(section_name) and (100<=section_id and section_id<=300)
| join type=outer left=L right=R where L.section_number=R.new_loc [search index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Move\sItem-(?<item>\w+\-\d+\-\d+\-\d+)\sfrom\sBin-(?<bin_id>\d+)-(?<slot_number>\d+)\sto\sAA-(?<new_loc>\d+)"
| where isnotnull(item)
| eval moved_at=strftime(_time, "%Y-%m-%d %H:%M:%S")
| table item, bin_id, slot_number, new_loc, moved_at]
| rename L.cart_id as cart_id, L.section_number as section_number, R.new_loc as new_loc, L.section_name as section_name, R.bin_id as bin_id, R.moved_at as moved_at
| table cart_id, new_loc, section_number, section_name, bin_id, moved_at