Query SQL x Virtual Layer x Joined Fields

Question

I have a spatial layer which I did a JOIN tool with a data table. Now in my attributes window I see all the fields: the original ones and the ones from the joined table.

Both are in a PostgreSQL database but when I create a virtual layer with a SQL query that fetches a column of the joined table, QGIS gives an error: there is no column "x" used in the query.

Is there any solution without definitively joining the layer with the table?

I am using QGIS 3.20 and Postgres 14.

Answer 1

Add the table and the layer in QGIS, then create a virtual layer that does the join and the filtering:

SELECT a.id, a.field1, 
       b.id, b.fieldXYZ,
       a.geometry
FROM myLayer a
 JOIN myTable b
  ON a.ref_id = b.id
WHERE b.fieldABC = 'abc'

You can use *,a.* and/or b.* to get all fields