I've one point layer in qgis and several non spatial tables in a spatialite database. Now I want to create a view as you can see in the following:

CREATE VIEW testansicht10
AS SELECT baumkataster.baum_ID AS ROWID,
baumkataster.geom AS geom,
gattung.gattung AS gattung,
art.art AS art,
sorte.sorte AS sorte,
baumkataster.b_hoehe AS b_hoehe,
baumkataster.kro_durchm AS kro_durchm,
baumkataster.kro_ansatz AS kro_ansatz,
baumkataster.age AS age
FROM baumkataster JOIN gattung ON baumkataster.gattung = gattung.gattung_ID
JOIN art ON baumkataster.art = art.art_ID
JOIN sorte ON baumkataster.sorte=sorte.sorte_ID;

When I do this the result is an empty view table though there are entries in my spatial table. If I exclude one of my joined tables (for instance "sorte") in the view I get the desired result - the view table with the existing entries. Is there a limitation in quantity of joined tables? The view should consist of about 65 coulomns (36 are from joined tables).

  • The maximum number of tables in a join is 64, according to sqlite.org/limits.html – Scro Jan 6 '14 at 20:06
  • Its easy to make a mistake with this sort of construct, and it almost always helps to break it down into pieces. Instead of trying to create a VIEW, just do the SELECT first. Instead of doing all the JOINs in one hit, just do one at a time. Try removing the art.art AS art and sorte.sorte AS sorte which might be a problem in the JOINs. – BradHards Jan 6 '14 at 23:16
  • Thanks a lot, I could fix the problem! As BradHards recommended I did it step by step. I also found out that there was a conflict with allowing NULL-values in one table but not in the other. Thats why I get a result when all fields are filled but not when one field has NULL-value. – parallax Jan 7 '14 at 19:52

Your Answer

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

Browse other questions tagged or ask your own question.