Match by location and attribute in QGIS?

Question

I have 2 different layers. One layer is the field layer which contains field agronomic data, name, variety, etc. The other layer is a layer with about 2000 polygons. Each polygon is tied to a variety. Some contain the same variety and are in different locations. Also there are some areas that have polygons stacked on top of each other that contain different varieties.

I want to determine if there are any fields inside a polygon that contain the variety inside the polygon. Another way of saying, I want to know if there are any fields inside the different polygons that have that variety.

I have tried join by location. (1:Many)Instead of telling me which polygons contain the field, I only get back how many polygons as a count. since I am not getting the information from the polygon layer, I cannot query if the varieties match.

I have also tried joining by attribute. Doing this will link up the varieties, but it is not spatially accurate.

I am using QGIS.

Answer 1

A Virtual Layer should do the trick (Layer|Create Layer|New Virual Layer) If your field layer was named Fields and contained the columns Field_Name and variety and the other polygon layer was named Regions and contained the columns Region_Name and variety then the SQL for the Query would be:

SELECT F.Field_Name, F.variety, R.Region_Name, F.geometry
FROM Fields F 
JOIN Regions R ON F.variety=R.variety AND ST_WITHIN(F.geometry,R.geometry)

This will create a query layer containing only the fields which are within polygons of the same variety.