MS SQL to MySQLconversion - Recursive CTE

Asked Oct 01 '13 at 11:11

Active Oct 01 '13 at 11:27

Viewed 235 times

i'm trying to convert this MsSQL to MySQL any idea how this can be done?

WITH Hierarchy(id, ciid1, name, parent, myLevel)
     AS (SELECT id,
                ciid1,
                name,
                parent,
                0 AS myLevel
         FROM   cities_new c
         WHERE  c.id = 2222
         UNION ALL
         SELECT c.id,
                c.ciid1,
                c.name,
                c.parent,
                ch.myLevel + 1
         FROM   cities_new c
                INNER JOIN Hierarchy ch
                  ON c.parent = ch.ciid1)
SELECT count(id) AS total
FROM   hotels_new
WHERE  id NOT IN( 69912, 70717, 75347 )
       AND ciid IN(SELECT id
                   FROM   Hierarchy)

edited Oct 01 '13 at 11:27

Martin Smith

438,706
87
741
845

asked Oct 01 '13 at 11:11

Zaphord

Similar thread http://stackoverflow.com/questions/7071333/mssql-to-mysql-data-transfer – Tom J Muthirenthi Oct 01 '13 at 11:15
i'm not trying to migrate MsSQL to MySQL just trying to convert the MsSQL query to MySQL – Zaphord Oct 01 '13 at 11:24
MySQL does not support recursive queries. Is there a known maximum hierarchy depth? – Martin Smith Oct 01 '13 at 11:30
i know that recursive queries are not supported thats my problem i need to find a work around for MySQL. The depth max is 7 – Zaphord Oct 01 '13 at 11:37

MS SQL to MySQLconversion - Recursive CTE

0 Answers0