Labelling from non-spatial database table in QGIS

Question

I have a PostGIS layer in my QGIS project whose id is related to a NON-SPATIAL table in the same database. I cannot join these tables into a new table or view, I have to leave the database exactly intact is it is.

Is it possible to create a labelling expression that will perform a tabular join on the fly inside the database to return the value?

Essentially I need return a label expression with something that would do this:

SELECT
 non_spatial_table.label
FROM
 spatial_table, non_spatial_table
WHERE
 spatial_table.key = non_spatial_table.key

Where spatial_table is the table driving the QGIS layer.

• Inactivated Account: Try creating a virtual layer in the qgis DB Manager which lets you build a SQL query on the 2 layers in your QGIS project then add that to the map for the labeling

Answer 1

You can do the join in QGIS, there is not need for a new table or view in the DB.

You can add the attribute table and the layer to QGS, then right click on the layer / properties / join / add join and add a new join using the attribute table and the common key.

When labeling, you will have access to the fields from both the layer and the attribute table. The joined fields are at the end of the list and are named as the table name + field name.