0

Could anyone please help me to change the below query to mysql format:

DECLARE @userTypeId INT;
SET @userTypeId = 1;

WITH tblChild AS
(SELECT *
        FROM category WHERE parent_id = @userTypeId
    UNION ALL
    SELECT category.* FROM category  
    JOIN tblChild  ON category.parent_id = tblChild.Id
)
SELECT *
    FROM products where category_id in (select id from tblChild)
OPTION(MAXRECURSION 32767)
James Z
  • 12,209
  • 10
  • 24
  • 44
Radhakanta Ghosh
  • 21
  • 1
  • 3
  • MySQL doesn't provide support for recursive or hierarchical data structures. You can do this with a loop in a stored procedure, but you can't do it with a single `select` statement. – Gordon Linoff Aug 12 '15 at 11:26
  • `MySQL` does not have `CTE`. Here's a good SO answer on how to recurse in `mySQL` http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159 – DhruvJoshi Aug 12 '15 at 11:27
  • possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) – Andy Aug 12 '15 at 23:38
  • Thank a lot - Gordon and DhruvJoshi for your valuable reply. – Radhakanta Ghosh Aug 13 '15 at 07:17

0 Answers0