How I can make a child table appear in the parent table of attributes in QGIS?

Question

I used "relate" to connect a many to one child table to a parent table (addresses to land parcels, one parcel can have several parcels) and I see it in the "form view but it is not displayed in the regular view in the attribute table, the same way a joined table would be (I don't see additional columns).

Is it normal?

How can I change this?

From what I understand "join" only work with one-to-one relationships.

Answer 1

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
FROM JoinLayerName
JOIN TargetLayerName
WHERE JoinLayerName.joiningattributefield = TargetLayerName.joiningattributefield

JoinLayerName.joinlayerattribute1, TargetLayerName.targetlayerattribute1 are the attributes from both layers you want to display in the virtual layer)

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).