Newbie - CREATE VIEW Question 2006-07-04 - By Jeremy Cole
Back
Hi,
> Say I have the following tables;
>
> siteID,name
> -- ---- ---- ---- ---- ---- --
> site1, XYZ
> site2, RSQ
>
> ID,site,data
> -- ---- ---- ---- ------
> 1, site1, M
> 2, site2, Q
> 3, site2, Y
> 4, site1, P
>
> ... etc.
>
> And I want to create a view like this;
>
>
> siteID,name,data
> -- ---- ---- ---- ---- ---- --
> site1, XYZ, (M,P)
> site2, RSQ, (Q,Y)
>
> where all the related column data in the second table is placed in another
> column. How can I do this? Is there a function that can group these values
> into one variable or array?
Requires 4.1 or higher:
SELECT
table1.siteID,
table1.name,
GROUP_CONCAT(table2.data SEPARATOR ",") AS all_data
FROM table1
JOIN table2 ON table1.siteID=table2.site
GROUP BY table1.siteID
Regards,
Jeremy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
