2

I've a point layer in qgis and serveral non spatial tables in a spatialite database. In qgis the fields were filled using the edit widget "value relation" (multiple selection allowed for some fields). I also created a custom form and conected some fields with a "List widget" in QT-Designer as you can see here, where one can choose more than one entry for a field.
Now in my "master table (baumkataster)" I got the following entries for one point feature (see example below):

baumkataster:    
baum_ID, boden_verd      
1,{2}   
2,{1,3}   
3,{1,2,3} 

In my query I joined the "master table" with a non spatial table (boden_ver):

boden_verd:   
boden_verd_ID,boden_verd   
1,value1   
2,value2   
3,value3

By now I've learned how to create a view to display the values not the key value. For testing I only do the SELECT statement. This works in case of single selection...

For example:

baum_ID,voden_verd   
1,value2   
2,value1   
3,value3 

...but it returns a empty table in case of multiple selection. How can I fix this to get the following result?

baum_ID,boden_verd   
1,{value2}   
2,{value3,value3}   
3,{value1,value2,value3}

I found the following code for a similar problem but it returns an error and I don't realy know whrer I'am wrong:

SELECT baumkataster.baum_ID AS baum_ID,   
baumkataster.geom AS geom,   

stuff((select  DISTINCT  ', ' +  boden_verd.boden_verd   
             from boden_verd
             WHERE ','+baumkataster.boden_verd+',' LIKE     '%,'+boden_verd.boden_verd_ID+',%'   
             for xml    path(''),type).value('.','nvarchar(max)'), 1, 2, '' )  AS    boden_verd    

FROM baumkataster;

Thanks, Patrick

  • I think I need to ask more precisely so I edited my question... – parallax Jan 15 '14 at 19:05
  • Unfortunately there is no function to split strings in SQLite and using a regular expressions approach, there seems to be no way to split strings like "{1,2,3}" and use every individual number. I suggest you create a table that just keeps the relations between baum and boden_verd by saving baum_IDs and boden_verd_IDs where applicable. – chrki Jan 26 '14 at 12:00
  • The thing is that the qgis edit widget "value relation" writes all chosen values in one attribute field when multible selection ist enabled. I am not shure how to manage this. Probably I'v to change the way of input - somthing like a check box. Then using a CASE expression in my query. But this would rise the number of fields. Any other ideas? Thanks! – parallax Jan 29 '14 at 13:18

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.