Join non-spatial CSV layer with spatial shape layer, using multiple input fields

Question

I have two separate layers i want to join in my QGIS-project based on multiple matching input fields. One layer consists of a non-spatial CSV table, but with the matching fields: Streetname, House number, Houseletter (and possible postalcode, but not needed i think). This layer is related to a subset from the next layer. The other layer consists of a spatial object layer, with object polygons (possibly) containing multiple adresses, also with Streetname, Housenumber, Houseletter.

Is it possible to join these two layers based on these three input fields? (So for example: Streetname, 1, A with Streetname, 1, A and Streetname, 2, *(NULL)* With Streetname, 2, *(NULL)*)?

The joined layer should contain everything from the non-spatial CSV and be joined with at least the polygon objects from the spatial layer.

Answer 1

You can achieve this using a virtual layer.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query.

Since some values can be null, you must use is not distinct from instead of = for the corresponding fields

select *
from my_polygon_layer p
 join my_csv_table t
  ON p.streetname = t.streetname
  AND p.HouseNumber = t.HouseNumber
  AND p.Houseletter is not distinct from t.Houseletter