Table join or virtual layer to match multiple points to geometry

Question

I have:

• a non-spatial table of multiple test results with reference to non-unique locations, and

• a spatial layer that has all the locations (multiple results per location)

I can do table joins between the locations layer to the results, but I get stuck because each location has multiple results...

I suspect I need to make a virtual layer assigning the geometry to each result, but I get lost at the "query" stage. The QGIS manual goes deep into database SQL queries but I am hoping there is a simpler solution for the non-initiated?

Note: I can create virtual "x" and "y" attribute columns and join them to my table, but still missing a step to use the coordinates in creating the virtual layer

Answer 1

Virtual layer is your answer :

for example two tables,

nonspatial table:
id|Restaurant|costumer
1 | Rest1    | george
2 | Rest1    | Peter
3 | Rest2    | grace
4 | Rest2    | Peter


spatial table:
id | Restaurant | geometry
1  | Rest1      | Point(1,0)
2  | Rest2      | Point(1,2)
3  | Rest3      | Point(2,0)

your query

select "nonspatial".*, "spatial"."geometry" 
from "nonspatial", "spatial"
where "nonspatial"."Restaurant"="spatial"."Restaurant"

Result

id|Restaurant|costumer|geometry
1 | Rest1    | george |Point(1,0)
2 | Rest1    | Peter  |Point(1,0)
3 | Rest2    | grace  |Point(1,2)
4 | Rest2    | Peter  |Point(1,2)

tip - if your tables and columns are in lowercase, quotes are not necessary