QGIS equivalent to Mapinfo SQL qery

Question

To run a simple interest query in Mapinfo I would use

Select * from _1_10000_map_sheet_key_map, Carpentaria_Shire where _1_10000_map_sheet_key_map.Obj Intersects Carpentaria_Shire.Obj into Selection

In QGIS in Add--Virtual Layer and in DB Manager I keep getting errors

select * from _1_10000_map_sheet_key_map where st_intersects (_1_10000_map_sheet_key_map.geometry, Carpentaria_Shire.geometry) as Selection

gives the following error

Query execution error on CREATE TEMP VIEW _tview AS select * from _1_10000_map_sheet_key_map where st_intersects (_1_10000_map_sheet_key_map.geometry, Carpentaria_Shire.geometry) as Selection: 1 - near "as": syntax error

The same from Virtual Layers and trying to use geom_from_wkt I get

Query preparation error on PRAGMA table_info(_tview): no such column: Carpentaria_Shire.geom_from_wkt

What's the expected structure of the query in QGIS SQL implementation?

Additionally can this be done in the Advanced Filter expression?

Answer 1

The SQL Select was one of the few things I missed about MapInfo when my work changed to QGIS.

You need to include the table "Carpentaria_Shire" in the FROM clause. Try this:

select A.*
from _1_10000_map_sheet_key_map AS A, Carpentaria_Shire as B
where st_intersects(A.geometry, B.geometry)

or for a different syntax:

SELECT A.*
FROM _1_10000_map_sheet_key_map AS A
JOIN Carpentaria_Shire AS B ON st_intersects(A.geometry, B.geometry)

To do it as an advanced filter expression:

intersects($geometry, aggregate('Carpentaria_Shire', 'collect', $geometry))