Qspatialite plugin problems with queries using inner join

Question

I'm trying to create a layer in QGIS from a sqlite database using a query from 2 tables: one has spatial data and the other doesn't.

When I build the query using Qspatialite using the 'Advance SQL Editor' it throws an error saying that the first column in the query has an ambiguous column name. Copying and pasting from an MSAccess query, it finds the matching records without a mutter.

Here are segments of the two queries:

MSAccess version (working)

SELECT Inspections.ID, Inspections.InspectionDate, Inspections.InspectionBy, Inspections.PathID, Inspections.Section, Inspections.GISLink, Inspections.Observations, Inspections.Notes, Inspections.ActionPriority, Sections.Geometry AS Geometry
FROM Inspections INNER JOIN Sections ON Inspections.GISLink = Sections.GISLink;

Qspatialite version (not working)

SELECT 'Inspections'.'InspectionDate',
'Inspections'.'InspectionBy',
'Inspections'.'PathID',
'Inspections'.'Section',
'Inspections'.'GISLink',
'Inspections'.'Observations',
'Inspections'.'Notes',
'Inspections'.'ActionPriority',
'Sections'.'GISLink',
'Sections'.'Geometry' AS Geometry
FROM 'Inspections', 'Sections'
inner join 'Inspections'
on 'Inspections'.'GISLink' = 'Sections'.'GISLink'

Could anyone give me an idea of what wrong's here - when I remove the INNER JOIN the query runs, but obviously gives me the duplicate values you'd expect!

Is this a problem with QSpatialite in the way it constructs the queries with '' all over the place?

I've just tested both queries in QGIS DB manager and same result the copy-paste version works, but the 'Advance SQL editor' version throws the same error...

Many thanks - and hoping that I can save someone else frustrating hours of SQL - I was assuming the differences in MSAccess would prevent the query from running in Qspatialite and as a last ditch I did the copy-paste. Doh!

I'm using QGIS 1.8.0 and QSpatialite 6.0.4

Answer 1

Your MS Vs SQLLite code is different on the join:

MS:

FROM Inspections INNER JOIN Sections ON Inspections.GISLink = Sections.GISLink;

SQLite:

FROM 'Inspections', 'Sections' inner join 'Inspections' on 'Inspections'.'GISLink' = 'Sections'.'GISLink'

You SQLite query is joining Inspections onto itself