Questions tagged [hierarchy]

For issues relating to arranging items into ordered levels.

Filter by
Sorted by
Tagged with
59
votes
3answers
69k views

Find highest level of a hierarchical field: with vs without CTEs

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 ...
2
votes
3answers
8k views

How to build a table for a private messaging system that supports replies?

I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply ...
2
votes
2answers
2k views

Better defining a parent-child relationship on a table

Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses. We first ...
16
votes
4answers
7k views

Traversing tree-like data in a relational database using SQL

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 than ...
2
votes
1answer
9k views

Get top most parent by nth child id?

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 ...
-1
votes
2answers
16k views

Return hierarchical data from a self-referencing table (parents, children)

I have the following SQL Server 2008 table and data: CREATE TABLE dbo.MyTable (Sno int, refid int); INSERT INTO dbo.MyTable VALUES (17, 8), (16, 8), (15, 7), (14, 7), (13, 6), (12, 6), ...
8
votes
2answers
16k views

Can I get a tree-structure from a self-referenced (hierarchical) table?

Given a hierarchical table like this: CREATE TABLE [dbo].[btree] ( id INT PRIMARY KEY , parent_id INT REFERENCES [dbo].[btree] ([id]) , name NVARCHAR(20) ); I would like to obtain the whole tree ...
1
vote
1answer
631 views

How to get last level of a hierarchical list, group by some field

Given the schema: create table containers ( id int primary key, name text, product_id int, lot int, qty int, parent_id int); create table orders_items ( id int, ...
21
votes
3answers
67k views

Parent-Child Tree Hierarchical ORDER

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, [FilterDesc]...
21
votes
5answers
8k views

Anybody using HierarchyId in production? Is it reliable?

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 ...
10
votes
6answers
5k views

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

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 ...
8
votes
3answers
19k views

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

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 ...
4
votes
2answers
974 views

Hierarchical queries without CTEs

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 ...