Multiple joined values in one row

[Peter Haworth-2]

Given the following tables:

TABLEA
KeyA,
DataA
TableBKey

TABLEB
KeyB
DataB

.. and a JOIN on TABLEA.TableBKey=TableB.KeyB

IS there a SELECT statement that returns TABLEA.KeyA,  
TABLEA.Data,AllDataB, where AllDataB consists of all the values of  
TableB.DataB strung together?

For example, for TABLEA.Key value of 1,and 3 corresponding entries in  
TABLEB - I'd want one row returned with the three values of  
TABLEB.DataB concatenated into the AllDataB column.


Pete Haworth









_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik]

Peter Haworth <pete@...>
wrote:

> Given the following tables:
>
> TABLEA
> KeyA,
> DataA
> TableBKey
>
> TABLEB
> KeyB
> DataB
>
> .. and a JOIN on TABLEA.TableBKey=TableB.KeyB
>
> IS there a SELECT statement that returns TABLEA.KeyA,
> TABLEA.Data,AllDataB, where AllDataB consists of all the values of
> TableB.DataB strung together?

You are looking for group_concat (http://sqlite.org/lang_aggfunc.html):

select KeyA, DataA, group_concat(DataB)
from TABLEA join TABLEB on TABLEA.TableBKey=TableB.KeyB
group by KeyA;

Igor Tandetnik


_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users