In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full). In general; A CTE can be used recursively; a sub-query … Read more
If you are hitting the recursion limit, you either have considerable depth in sponsoring relationships or a loop in the data. A query like the following will detect loops and terminate the recursion: declare @tblMember as Table ( MemberId Int, SponsorMemberId Int ); insert into @tblMember ( MemberId, SponsorMemberId ) values ( 1, 2 ), … Read more
Try this: ;WITH items AS ( SELECT EstimateItemID, ItemType , 0 AS Level , CAST(EstimateItemID AS VARCHAR(255)) AS Path FROM EstimateItem WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID UNION ALL SELECT i.EstimateItemID, i.ItemType , Level + 1 , CAST(Path + ‘.’ + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255)) FROM EstimateItem i INNER JOIN items itms … Read more
Use data-modifying CTEs: WITH ins1 AS ( INSERT INTO sample(firstname, lastname) VALUES (‘fai55’, ‘shaggk’) — ON CONFLICT DO NOTHING — optional addition in Postgres 9.5+ RETURNING id AS sample_id ) , ins2 AS ( INSERT INTO sample1 (sample_id, adddetails) SELECT sample_id, ‘ss’ FROM ins1 RETURNING user_id ) INSERT INTO sample2 (user_id, value) SELECT user_id, ‘ss2’ … Read more
Update: MySQL 8.0 is finally getting the feature of common table expressions, including recursive CTEs. Here’s a blog announcing it: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/ Below is my earlier answer, which I originally wrote in 2008. MySQL 5.x does not support queries using the WITH syntax defined in SQL-99, also called Common Table Expressions. This has been a feature … Read more
It depends. First of all What is a Common Table Expression? A (non recursive) CTE is treated very similarly to other constructs that can also be used as inline table expressions in SQL Server. Derived tables, Views, and inline table valued functions. Note that whilst BOL says that a CTE “can be thought of as … Read more
I haven’t tested your code, just tried to help you understand how it operates in comment; WITH cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( –>>>>>>>>>>Block 1>>>>>>>>>>>>>>>>> — In a rCTE, this block is called an [Anchor] — The query finds all root nodes as described by WHERE ManagerID IS NULL SELECT EmployeeID, FirstName, LastName, … Read more
You can do it in a single call from php to mysql if you use a stored procedure: Example calls mysql> call category_hier(1); +——–+—————+—————+———————-+——-+ | cat_id | category_name | parent_cat_id | parent_category_name | depth | +——–+—————+—————+———————-+——-+ | 1 | Location | NULL | NULL | 0 | | 3 | USA | 1 | Location … Read more
MySQL prior to version 8.0 doesn’t support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle), so you are left with using: TEMPORARY tables DERIVED tables inline views (effectively what the WITH clause represents – they are interchangeable) The request for the feature dates back to 2006. As mentioned, you provided a … Read more