Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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