Geometry loss/mix up of SpatiaLite view joining two tables

Question

I have two SpatiaLite tables:

• Sites (with geometry column) (ca. 5000 single sites)
• Samples (ca. 6800 samples)
  To display the samples in QGIS I want to create a spatial view showing all samples at their respective locations from sites joined using the attribute field ID_site, however, the geometry is not joined (correctly) for all samples.

a) View Composer of spatialite_gui joins the tables using 'ID Site' and using rowid as the views_geometry_column - but always shows a "filtered view" displaying only the sites. QGIS shows a "filtered view" restricted to the number of sites. All locations with more than one sample can not be accessed in QGIS via "Zoom to Feature/Flash Feature". Following the advice of https://gis.stackexchange.com/questions/254713/spatialite-view-appears-filtered-in-attribute-table-wont-load-in-canvas> Update SQL layer in QGIS with other unique(!) fields for all samples (e.g. id_sample or rowid from the sample table) do not change the result.

Variation of the join (FROM samples JOIN sites ON ... to FROM sites JOIN samples ON ...), variation the type of join (join, left join) as well as reassigning the id in QGIS via Right click layer Update SQL Layer do not result in the correct display of all samples.

b) by hand
Creating the View via SQLite and registering the view by hand (id_samples correctly registered in the view_geometry_columns table and updated via "Update SQL Layer" in QGIS as suggested here Spatialite view appears 'filtered' in attribute table, won't load in canvas ) shows all 6800 samples in QGIS, however, their location is mixed up as the following images of a rectangular selection ares shows

CREATE VIEW view_join AS
    SELECT spls.ID_site,
           spls.ID_sample,
           sites.ID_site,
           sites.site_nom,
           sites.geometry
      FROM Samples AS spls
           JOIN
           Sites AS sites ON (spls.id_site=sites.id_site);
INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid,f_table_name,f_geometry_column,read_only)
VALUES ('view_join', 'geometry','id_sample','sites','geometry',0);

I am sure I am not the first one to join many samples to a few sites thus this complex problem for a standard routine took me by surprise or else I am missing something quite simple. The problem is probably related to the joining or the adressing of the geometry column but having been through all variations I am at a loss.