Performing many to one join in QGIS

Question

I have read https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/attribute_table.html#creating-one-or-many-to-many-relations and also many to one join in QGIS but I can't seem to be able to do a many to one join in QGIS 3.4.

I have many addresses that I want to connect to land parcels, one land parcel can have several addresses on it, some land parcels also don't have addresses (like farmland for instance).

I went to project/properties and added a relation between these two layers (which have a common identifier (the land parcel ID is already connected to each address in the address attribute table, so I used this same info for the referencing and referenced fields), but then I don't see new fields from the address table be displayed in the parcel table and I have no idea how to have it displayed (QGIS documentation talks about "forms for 1-N relations" but it doesn't seem to refer to the attribute table per se. I actually see the child table (address) appear in form view in the parcel attribute table, but in any case I don't seem to be able to add all the features of the address table, the "add child feature" doesn't seem to be doing that even when selecting all features.

Ideally I would like to have the addresses table joined in the parcel table, with new rows for the same parcel with a different address having been created, and rows of land parcels without address left empty (save the parcel info).

Answer 1

Transform the layer to join into a csv, then use Combine/Attributes join using CSV file in the MMQGIS plugin. This creates a new shapefile with added rows as needed for the many to one relationship (for instance, if 2 cities are related to 1 country, unlike the regular join tool from QGIS which only keep 1 city if there was just 1 country row, this will create 2 country rows, 1 for each city).

I didn't need to use the "relate" tool at all, which doesn't help since it only connects tables in the form view and doesn't perform a real join.

Please note that this is only for many to one relationship. If you want to do a one to many join (for instance one piece of land joined to many addresses), the regular join tool works fine (it will populate all address rows with corresponding piece of land, even if they are several instances of the same parcel).

Another way of doing this (although I was not able to keep geometry but it might not be related to the process itself and I don't really find it easier) is to create a virtual layer:

One way is to use virtual layers (thank you @johns):

1. Layer/Create Layer/New Virtual Layer
2. Import the layers you want to join
3. Write SQL script, then test, then click "add". Please note that it seems SQL doesn't support layers with spaces, so if you have "layer 1" rename or export to a new layer "layer_1" first. You can also replace an attribute name in SELECT by * to select all attributes, for instance JoinLayerName.*

SQL script is (at least it worked for me):

SELECT JoinLayerName.joinlayerattribute1, TargetLayerName.targetlayerattribute1 (= attributes from both layers you want to display in the virtual layer) FROM JoinLayerName JOIN TargetLayerName WHERE JoinLayerName.joiningattributefield = TargetLayerName.joiningattributefield

You can then export the virtual layer to a new geopackage or shapefile, the only thing is that I could georeference the file but I was not able to see it (it was invisible, the polygons from the original file don't seem to be there). I am unsure how you could keep the geometry as well, I have to investigate this more (unless someone knows how to do it).