SpatiaLite database error: no such table, but it is present in the SpatiaLite database

Question

I am new to databases in QGIS, but cannot find any answer in all the documentation and online help in a weeks worth of desperate searching. I think this might be a newbie error, but just cannot solve this issue.

I am trying to join two attribute tables from two point shapefiles [j_pipeline_inc (multipoint) to the j_pipeline_midpts (point)] using a SQL expression (image attached):

SELECT j_pipeline_midpts.*, j_pipeline_inc.*
FROM j_pipeline_midpts
INNER JOIN j_pipeline_midpts
on j_pipeline_midpts.LSD = j_pipeline_inc.LOCATION and j_pipeline_midpts.Licence_No=j_pipeline_inc.LICENCE_NO

The join will be based on two keys (LSD & Location, Licence_No & LICENCE_NO). I have created a SpatiaLite database (incident_move.sqlite), which has these two point shapefiles in it, but when I execute my expression it states: "no such table: j_pipeline_inc". I don't understand why it doesn't recognize one of the files. When I type their names in the expression box it shows both shapefile names in the autocomplete.

I have tried saving one file as a SpatiaLite file type then importing the other to that database, but it still gives me the same error.

Also, they both have the same projection (EPSG:3400 - NAD83).

Answer 1

It is a simple mistake in the table name. Change the table name to j_pipeline_inc after INNER JOIN. Try this:

SELECT j_pipeline_midpts.*, j_pipeline_inc.*
FROM j_pipeline_midpts
INNER JOIN j_pipeline_inc
on j_pipeline_midpts.LSD = j_pipeline_inc.LOCATION and
j_pipeline_midpts.Licence_No=j_pipeline_inc.LICENCE_NO