The code below (in blue) works fine when run against an MS SQL table however when run through MYSQL Query Browser against a MySql copy of the table I get the following error...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @MyEmailString varchar(8000)

select @MyEmailString = coalesce(@MyEmai' at line 1

declare @MyEmailString varchar(8000)

select @MyEmailString = coalesce(@MyEmailString + ',', '') + coalesce(FFNumber, '')
from dbo.EMPLOYEES_Registered
where EmpGrade = 'CMGR'

select @MyEmailString as MailingList

I'm using MySQL version 5.1.9 on Linux.

Any ideas?

Thanks,

Dave

you may need to have a BEGIN-END block

when in doubt, check the manual

see http://dev.mysql.com/doc/refman/5.1/...variables.html

(disclaimer: i haven't written any mysql stored procs myself)

I'm pretty sure that you need to use GROUP_CONCAT, at least based on your original question. I'm rather surprised that R937 didn't respond there (you're slipping Rudy!).

-PatP

yeah, maybe i am slipping

i answered the question that was asked again

i guess i failed to look deeper into the stored proc to discover that there was an even better solution, to a question that wasn't asked, namely, "is there a better way to do this?"

of course, as pat will tell you, you should never do grouping on the server anyway, you should do it on the client side, therefore neither the stored proc nor the GROUP_CONCAT is the correct solution

Thanks guys.

I had tried the Begin / End already without success but I can now scrap that as Group_Concat works perfectly.

To complicate things....is it possible to do a union within a Group_Concat statement. i.e. I may need to build a list of user id's from two different tables.

Thanks again,

Dave

i'm sure it can be done, but perhaps not with the UNION actually within the GROUP_CONCAT

but why are you using mysql? pat says it isn't relational, so maybe you'd better reconsider, and just stick with SQL Server

All our db's currently sit on MySQL. We will be migrating to MS SQL but only gradually and in the meantime I have to find a solution for MySQL.

I'm in the process of setting up a number of DTS packages on the MS SQL server which pull data from MySQL. When I was testing the 'Coalesce' solution earlier I copied the required tables to the MS SQL server temporarily.

If you can think of how I could merge the results of two tables on MySQL it would be a great help. I tried a simple union however that only gave me two rows of data.

Thanks,

Dave

i would use UNION

I've tried UNION but it returns two rows of data and I need one row.

select group_concat(FFNumber)
FROM ERRORS_AccessControl
WHERE Role = 'MGR'
UNION
select group_concat(FFNumber)
FROM EMPLOYEES_Registered
WHERE Role = 'MGR'

Brilliant....thanks so much for all the help. Hope I can return the favour sometime. Dave