Filter features based on a no-geom table

[CarstenHogertz]

Hello,

I've got two datasets in my current map:

1. a FeatureLayer (locations of measuring points for air quality data)

2. a table without geom (holds the air quality measured data for each feature)

What I'd like do is the following:

I use a definition query to filter the no-geom table (for example only want to see the measured ozon data)

As not all measuring points have ozon data I'd like the FeatureLayer to be filter based on no-geom table.

How could I achive that. In SQL would be something like: where location in (select distinct (column) from no-geom table).

But I could find a solution in APRO.

Every hint is welcome.

Thanks alot!

[RobertBorchert]

there is a definition query that can query out related data. I use it quite often for just your purpose.

The field names need to be database names and not alias 

ORIGINFIELD IN (SELECT DESTINATIONFIELD FROM <RELATED NAME> where <FIELDNAME> <QUERY>)

In this example I have some 9000 points. I only want to see points that have a related table value where location is a positive integer

LOCATION in (Select LOCATION FROM SitePhotos_WORKGROUPS where LOCATION > 0)

The first location is the LOCATION field in my point feature this should be the same as the Origin Key in your relationship

The second LOCATION  is the Destination Key field in my related table

SitePhotos_WORKGROUPS is the name of the related table

[CarstenHogertz]

Thanks @RobertBorchert  Does your solution work on services as well?

[CarstenHogertz]

One thing is missing in your proposed solution (maybe because I didn't communicate my requirements clearly): 

- My no-geom table itself uses definition queries in APRO to filter it's content
- Your SQL communicates directly with the DB

Well, the consequence is that I can see all locations that have an entry in the no-geom db-table. But what I'd like to do is seeing only that locations that are in the APRO filtered no-geom table.

Do you have a solution for that?

[RobertBorchert]

The project I use this solution for there are some 9000 point features with Location Numbers for our company. The project allows field crews to take photos of various sites for a variety of reasons.  In a Dashboard the people in the office can find that site and see the photos, with a variety of other filters. 

This definition query only shows the points with photos.  Currently some 900

However, I do not know if this will work with AGOL or services hosted in the Cloud. We are an Enterprise Portal