Outer Join removing features

[BrianBulla1]

Hi,

I have a table with a definition query (to filter based on a date field) and a point feature class.  When I join the table to the feature class I select "Keep all Features" (ie. an Outer Join) but after the join finishes I am only left with the features that have a related record in the table.

Isn't "Keep all Features" supposed to do just that....keep all the features??

Before the join:

After the join:

Can anybody explain what is going on?  This is with ArcGIS Pro 2.8.1

[RobertBorchert]

Apply the definition query after the join

[BrianBulla1]

But when I do that then all of those features I am missing are still missing....but this time because of the definition query.  The table is a table of inspections for the features.  Each feature has an inspection record in there dating back many years, but I am only interested in the inspections for the current year.

What I need to see are all of the features (ie. hydrants) that have an inspection record during 2022.

But I also need to keep all of the hydrants that don't have an inspection record in 2022....which is what I thought the "Keep all Features" would do.  I could then symbolize based on the joined INSP_DATE field.  All of the INSP_DATE = Null would be the ones that haven't been inspected yet.  

I could do this with two separate layers....one layer with ALL of the hydrants as "Uninspected" and then the layer with just the inspected layers on top of it.  Visually, it works, but when I load it into a web map, when a user clicks on an inspected hydrant, they are actually seeing a pop-up for the same hydrant twice....one pop-up from the Uninspected layer (which they can't see) and one pop-up for the top Inspected layer.

There has to be a way to show both Uninspected and Inspected in the same layer.  

[Jen_Zumbado-Hannibal]

Hi Brian, 

Moments ago, I posted the solution to one of my questions based on your post. It turns out that OUTER JOIN doesn't work all that well in ArcPro. 

Look at my post and see how to do a definition query based on a point layer and a table. Maybe my SQL query can give you some ideas on how to modify yours. 

Sometimes seeing someone else's example may spark an idea of your own. 

Good luck

[BrianBulla1 · Accepted solution]

Hi.  

Ok, so I have this working.  What I did was remove my table and replace it with a Query Layer, using SQL to basically only show me the 2022 Inspections.

Joining that query layer to the hydrants feature class seemed to work, and I can now symbolize my hydrants using the <null> values in the join to show me the 'uninspected' hydrants.

Thanks!!

[Jen_Zumbado-Hannibal]

I don't know much. But, glad I could help.