[Qgis-user] 1 to many relationship does not allow to display the many

[Janneke van Dijk]

Dear list,

I have a parent - child relationship set up between a table location (1, 
the parent) and a table assets (many, the child). The location table 
contains geometry, the asset table does not. I would like to be able to 
display each record in the asset table with the corresponding geometry 
of the location table - so if there are 3 assets at a certain location, 
I would like to display 3 points (each with identical geometry).

What I can do with the DB manager is:

select location_point.geom, asset_point.assetid, location_point.fid  
from asset_point cross join location_point on asset_point.locationid = 
location_point.fid

this query yields 170 records, corresponding with the number of records 
in the assets table. When I load as a new layer, the result is a table 
with 170 records, but 100 filtered (the total number of locations). I 
chose assetid to be the unique id (which it is).

Is that expected behaviour? How else can I display all the records in 
the assets table (instead of only the 100 that are filtered now)? I know 
I can do it in Postgres but for my purpose now I don't want to go there.

Any insights deeply appreciated!

Janneke