mssql to mysql query conversion

[chris Sellers]

I am trying to get started in converting some queries from mssql to mysql.
The information I have gathered so far seems that I need to change the "stuff" statements to group_concat. I am unclear on if the cast statements need modified. and I have tried to modify this darn thing to what I believe it should be, but I can't get it to run! Arrrgg! Anyone have some advice for this conversion? There is no query migration tool that I can find, which seems logical given the complexity of such a tool. I would like to add I know nothing of mssql, but I am fair at mysql.

Here is one of the queries in question.

SELECT DISTINCT c.name Cluster, s.name Server. stuff( ( SELECT cast(', ' as varchar(max)) + sc2.name FROM Server_contact AS sc2
WHERE sc2.Server_ID=s.Server_ID ORDER BY sc.name
for xml path('')
), 1, 1, '')
AS 'Server Contact',
(SELECT c.name FROM Cluster c WHERE c.Cluster_ID=s.virtual) 'Virtual on', a.name Application,

stuff( ( SELECT cast(', ' as varchar(max)) + ac2.name FROM app_contact AS ac2
WHERE ac2.App_ID=ac.App_ID ORDER BY ac.name
for xml path('')
), 1, 1, '')
AS 'Application contact',
stuff(
(
select cast(',' as varchar(max)) + s3.name
from Server s3
LEFT JOIN App_affect apaf on apaf.App_ID=a.App_ID
LEFT JOIN affected aff on aff.aff_ID=apaf.aff_Id
WHERE aff.Server_ID=s3.Server_ID AND apaf.Server_ID='".$_REQUEST['server']."'
ORDER BY s.Name
for xml path('')
), 1, 1, '')
AS 'App Affected Server',

stuff(
(
select cast(',' as varchar(max)) + a3.name
from Application a3
LEFT JOIN App_affect apaf on apaf.App_ID=a.App_ID
LEFT JOIN affected aff on aff.aff_ID=apaf.aff_Id
WHERE aff.App_ID=a3.App_ID AND apaf.Server_ID='".$_REQUEST['server']."'
ORDER BY s.Name
for xml path('')
), 1, 1, '')
AS 'App Affected Application'

FROM Server s
LEFT JOIN Server_Cluster scl ON s.Server_ID = scl.Server_ID
LEFT JOIN Cluster c ON c.Cluster_ID = scl.Cluster_ID
LEFT JOIN Server_contact sc ON s.Server_ID = sc.Server_ID
LEFT JOIN Server_App sa ON s.Server_ID = sa.Server_ID
LEFT JOIN Application a ON a.App_ID = sa.App_ID
LEFT JOIN App_contact ac ON a.App_ID = ac.App_ID
LEFT JOIN Server_affect saf ON s.Server_ID = saf.Server_ID
LEFT JOIN Affected aff ON aff.aff_ID = saf.aff_ID
WHERE s.Server_ID='".$_REQUEST['server']."'
ORDER BY c.name DESC, s.name ASC
";



Edited 3 time(s). Last edit at 07/25/2012 07:21AM by chris Sellers.