How to query multiple values from a joined table in a select statment?

Question

I’ve the following tables in a spatialite database:

This tables are filled as follows:

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

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

What I need ist the following:

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

I found a code-example (already adapted for my needs) 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,

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;

Is this possible? Thanks for your answers!!
Patrick

What error does it return? Your WHERE clause is unlikely to work for any SQL dbms, and it's open to SQL injection attacks. — Mike Sherrill 'Cat Recall', Jan 26 '14 at 11:50
Are you using SQLite? If yes you are looking for `group_concat()`: http://stackoverflow.com/questions/18778844/group-concat-in-sqlite — , Jan 27 '14 at 14:51

score 0 · Accepted Answer · answered Jan 27 '14 at 14:45

SQLite's SELECT statement doesn't support any syntax like "for xml path()".

In a SQL database, you should expect to store values like this

baumkataster:   
baum_ID  boden_verd      
1        2
2        1
2        3
3        1
3        2
3        3

or like this.

baumkataster:   
baum_ID  boden_verd      
1        value2   
2        value3
2        value3
3        value1
3        value2
3        value3

Exceptions to this are relatively rare. (And supported by a dbms that provides xml functions or array functions.)

How to query multiple values from a joined table in a select statment?

1 Answers1