how to join tables

[weronpc]

I have 2 tables with the following schematic:

TABLE 1

ID Name Description
==============================
1 John Tall
2 Mike Small


TABLE 2

ID Element
==================
1 Red
1 Green
1 Yellow
1 Blue
2 Red
2 Green
2 Yellow
2 Pink


I want to create a view / or output that concatenates _all_ the elements from Table 2 into one single field that can be placed on the end of the Table 1 (regardless of how many elements).

Like the following:


OUTPUT / VIEW

ID Name Description Element
=================================================
1 John Tall Red Green Yellow Blue
2 Mike Small Red Green Yellow Pink


Your help would be appreciated,

Mike

[Tangerine Dream]

Hi,

SELECT t1.*, GROUP_CONCAT(t2.Element SEPARATOR ' ')
FROM Table1 AS t1 INNER JOIN Table2 AS t2
ON t1.Id = t2.Id GROUP BY t1.Id;


Also check 12.9.1. GROUP BY (Aggregate) Functions (http://dev.mysql.com/doc/mysql/en/group-by-functions.html) for GROUP_CONCAT function