convert mssql query to mysql query

Question

I have the following mssql query that I found on the net that is supposed to help me with a complex mysql query that I have been struggling with for a few days now.

SELECT
    inv.typeID AS typeID,
    inv.typeName AS typeName,
    invGroups.groupName AS groupName,
    inv.published AS published,
    inv.description AS description,
    rankVal.valueFloat AS rank,
    replace (( SELECT skills.attributeName AS [data()]
      FROM dgmTypeAttributes tattr  -- Link between skillbook and attributes
      INNER JOIN dgmAttributeTypes skills ON (skills.attributeID = tattr.valueInt)
      WHERE (tattr.typeID = inv.typeID)
        AND (tattr.attributeID IN (180, 181)) -- Primary and secondary attributes
      ORDER BY inv.typeID FOR xml path('')), ' ', ',') AS prisec,
    replace (( SELECT RTRIM(CAST(inv2.typeID AS varchar)) + ',' AS [data()]
      FROM (SELECT * FROM dgmTypeAttributes WHERE (attributeID in (182, 183, 184)) -- Pre-req skills 1, 2, and 3
        AND (typeID = inv.typeID)) tattr2 
      INNER JOIN invTypes inv2 ON (tattr2.valueInt = inv2.typeID)
      ORDER BY inv.typeID FOR xml path('')), ' ', ' ') AS prereq,
    replace (( SELECT RTRIM(CAST(tattr2.valueInt AS varchar)) + ',' AS [data()]
      FROM (SELECT * FROM dgmTypeAttributes WHERE (attributeID in (277, 278, 279)) AND (typeID = inv.typeID)) tattr2  -- Link between skillbook and attributes
      ORDER BY inv.typeID FOR xml path('')), ' ', ' ') AS prereqlvl
FROM invTypes inv
INNER JOIN invGroups ON (inv.groupID = invGroups.groupID)
INNER JOIN dgmTypeAttributes rankVal ON (inv.typeID = rankVal.typeID)
WHERE invGroups.categoryID = 16 -- Skillbooks category    
    AND rankVal.attributeID = 275 -- Skill rank attribute
    AND inv.published = 1
GROUP BY inv.typeID, inv.typeName, invGroups.groupName, inv.published, inv.description, rankVal.valueFloat
ORDER BY invGroups.groupName, inv.typeName

I am so so with mysql but I know nothing of mssql. Can somebody recommend a good method of converting this query that is low or now cost? I do not expect somebody to convert it for me as that would be asking too much, but some suggestions that would point me in the rite direction (aside from learning mssql lolz) would be very nice. Thank you for your time and patience.

score 0 · Answer 1 · answered Aug 13 '11 at 02:21

0

'Recommendation: extract the data out of you MySQL database in a delimited file (csv) using the utf8 (unicode) character set. Import into SQL Server using bcp specifying utf8 with "-Jutf8" parameter and character mode "-c".' See this site. Also, there's a nice tool for this.

answered Aug 13 '11 at 02:21

djhaskin987

9,741
4
50
86

I think you have it backwards. I have the data in a mysql db, it was originally stored in a mssql db but for the mass of users that actually use the data, we have it exported to mysql. How ever, there are enough users that you often find queries that are helpful with working in the data in mssql and to somebody that works with mysql they are quite useless. Thank you though. Things to add. I have no access to mssql. I have no access to the mssql datadumps even if i had access to mssql server – Wes Aug 13 '11 at 02:24

score 0 · Answer 2 · answered Aug 13 '11 at 02:27

0

Those subqueries with FOR XML PATH('') seem to be used to concatenate strings1. See if you can replace them with GROUP_CONCAT in MySQL. The other bits seem to be standard SQL.

answered Aug 13 '11 at 02:27

lmz

1,560
1
9
19

it appears that it does not like the [data()] either. is that the same as creating an alias? – Wes Aug 13 '11 at 02:52
@Wes: Square brackets are used for delimiting an identifier (whether a name or an alias) in SQL Server, similarly to how ticks (`\``) are used in MySQL. – Andriy M Aug 13 '11 at 03:56

convert mssql query to mysql query

2 Answers2

Linked