Well, I tried some different requests to simplify. This one is OK in all cases :
SELECT * FROM Pylones P JOIN Lignes L ON L.lig_id = P.lig_id JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid; But this one gives correct results only with Spatialite GUI (Sqlite version 3.7.3) or QGIS (Sqlite version 3.7.17) but wrong results with System.Data.Sqlite (version 3.8.5). SELECT * FROM Pylones P JOIN Lignes L ON L.lig_id = P.lig_id JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid WHERE L.lig_id = 3; All is right when I join only 2 tables : SELECT * FROM Pylones P JOIN Lignes L ON L.lig_id = P.lig_id WHERE L.lig_id = 3; SELECT lig_id, lig_nom, lig_geom FROM Lignes L JOIN idx_Lignes_lig_geom I ON (L.lig_id = I.pkid AND I.xmax >= 910202.5 AND I.ymax >= 6506392.5 AND I.xmin <= 924587.5 AND I.ymin <= 6513427.5); -----Message d'origine----- De : [email protected] [mailto:[email protected]] De la part de Simon Slavin Envoyé : lundi 28 juillet 2014 15:43 À : General Discussion of SQLite Database Objet : Re: [sqlite] System.Data.Sqlite On 28 Jul 2014, at 1:01pm, Eric DAVID <[email protected]> wrote: > I have a problem using System.Data.Sqlite in a C# application. I have > 2 tables, each table with a geometry field and a spatial index, so the > following request give me the results I want using Spatialite GUI or QGIS : > > > > SELECT * FROM Pylones P > > JOIN Lignes L ON L.lig_id = P.lig_id AND L.lig_id IN (3,4) > > JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid AND I.xmax >= > 910202.5 AND I.ymax >= 6506392.5 AND I.xmin <= 924587.5 AND I.ymin <= > 6513427.5 > > > > But, when I try to use the same request with System.Data.Sqlite, I > only obtain one record (the first one). Can you simplify this ? Perhaps get rid of one of the JOINed tables, or remove one of the clauses until it is a clear demonstration of an error ? Also, can you check to see which versions of SQLite each of your tools is using ? The output from "SELECT sqlite_version()" would be fine. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
