MySQL ODBC Connector - Creating a string from a resultset directly in SQL ?

Author

Creating a string from a resultset directly in SQL ?

Henri-Maxime Ducoulombier

2005-09-16, 7:23 am

Hello there,

I have a question regarding the possibility of creating a string from a
resultset directly using an SQL command. I'm not sure if this is possible.

Here is what I have:
A table with links between articles and category in a catalog.
For ArticleID #1, I have say 3 categories, and I would like the list of
categories like that : 57,42,36

So I was trying to do something like that :
SELECT Concat_WS(',', (SELECT CatID FROM tblcatarticles WHERE ArticleID = 1))

But it obviously won't work since the subquery returns more than 1 row. My
question is : is there anyway to do this in one request or do I have to
browse my recordset and build my list using my favorite programming tool ?

Thanks!
HMax

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jasper Bryant-Greene

2005-09-16, 7:23 am

Henri-Maxime Ducoulombier wrote:
> I have a question regarding the possibility of creating a string from a
> resultset directly using an SQL command. I'm not sure if this is possible.
>
> Here is what I have:
> A table with links between articles and category in a catalog.
> For ArticleID #1, I have say 3 categories, and I would like the list of
> categories like that : 57,42,36
>
> So I was trying to do something like that :
> SELECT Concat_WS(',', (SELECT CatID FROM tblcatarticles WHERE ArticleID = 1))
>
> But it obviously won't work since the subquery returns more than 1 row. My
> question is : is there anyway to do this in one request or do I have to
> browse my recordset and build my list using my favorite programming tool ?

Take a look at the GROUP_CONCAT function, under "functions for use with
GROUP BY clauses" in the MySQL manual. You'll need MySQL 4.1+ AFAIK.

--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Henri-Maxime Ducoulombier

2005-09-16, 7:23 am

>Take a look at the GROUP_CONCAT function, under "functions for use with
>GROUP BY clauses" in the MySQL manual. You'll need MySQL 4.1+ AFAIK.

Thanks a lot Jasper, that is EXACTLY what I was looking for !

Cheers

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links