1

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).

view of my SQL expression and SpatiaLite database

  • Database Administrators is a better fit for pure SQL questions. – Vince Aug 17 at 13:57
  • I have created a SpatiaLite database (incident_move.sqlite), which has these two point shapefiles in it, if the data from the shapefiles is in a spatialite database, they are no longer shapefiles. – nmtoken Aug 19 at 9:14
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
| improve this answer | |
  • Thank you. What a simple fix. SQL is a very powerful tool, but I am new and make these little mistakes that cause sych frustration. – Matthew M Aug 17 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.