note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier ...
Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
I have to following data in SQL Server 2008 R2. SQLFiddle Schema: CREATE TABLE [dbo].[ICFilters]( [ICFilterID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL DEFAULT 0, [...
Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES ...
I'd like to do the following in one query using MySQL: grab a row that has a parent_id of 0 grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0 ...
I'm new to MySQL. The GetFamilyTree(id) function that Rolando suggested in the Find highest level of a hierarchical field: with vs without CTEs question was great but when the id is greater than 999 ...