Questions tagged [hierarchy]
For issues relating to arranging items into ordered levels.
144
questions
0
votes
0answers
22 views
Sql subquery for nested parent child data
I have two tables , on parent table (categories ) and child table (features).. I have this query
select c.* ,
(select GROUP_CONCAT(f.featuresId , f.featuresTitle )
from ...
2
votes
1answer
33 views
How to select id of father from the child id recursively
I have in the field cod_categorizacao_pai the ID of the father, and I need to mount a select that just enter an ID and it will list the paternal IDs of this ID until it reaches the parent of all. How ...
2
votes
1answer
79 views
How to query all entities associated with all children (recursive) of a node in SQL?
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.
Ok, I’m having trouble putting this into words… so bear with me.
Say I have a tree of ...
0
votes
0answers
50 views
Worked example of closure tables with from and thru dates
I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now.
...
3
votes
3answers
238 views
SQL Server CTE Bottom to Top Recursive with Where clause
I have an Employee Table with an EmployeeId, ManagerId and a Name field.
The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).
I found ...
4
votes
3answers
64 views
How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query?
I have a table with categories and another table with a discount for categories per user:
create table category
(
id NUMBER NOT NULL,
parent_id NUMBER,
name nvarchar2(255),
PRIMARY KEY( id)
)...
3
votes
0answers
45 views
Hierarchical database schema design - X500 data model [closed]
I have to make a centralized database for appliance configurations, and SQL is a no-go because was evaluated as too expensive in terms of resources for read-mostly load.
We planned to use ...
1
vote
1answer
87 views
Hierarchical / tree data - which type of database to use & design?
For querying (and writing to) a hierarchical structure (like a folder containing files and other folders, up to n levels deep, where n is pretty small, probably around 10, and there would be lots of ...
0
votes
1answer
69 views
Advantages of a hierarchical schema for private chat
This post claims that
All modern chatting interfaces, without exception, implement a
hierarchical and a not-chronological schema for chat.
It also links another post which gives an example ...
0
votes
0answers
150 views
How to create HierarchyId node constraint without GetAncestor table column?
I have a table similar to the following that holds hierarchies from difference sources (SourceId).
CREATE TABLE dbo.Hierarchies
(
SourceID INTEGER NOT NULL,
NodeHierarchyId HierarchyId NOT ...
2
votes
1answer
484 views
Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation
I have a table with Parent-Child Relation in the same Table. Some Sample Data
id │ name │ parent │ is_active
════╪══════════════════════╪════════╪═══════════
1 │ Company A ...
3
votes
0answers
329 views
How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)
I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
0
votes
1answer
182 views
Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | ...
0
votes
1answer
262 views
How make recursive json on mysql
my table looks like
CREATE TABLE `test_comments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned DEFAULT NULL,
`test_id` int(10) unsigned NOT NULL,
`user_id` int(...
1
vote
0answers
125 views
How to get the hierarchy of particular employee using JPA spring boot?
I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot.
The problem is similar to the following link
Similar Data Model
If A reports to B and B reports ...
2
votes
0answers
43 views
Design for a child to have a single parent `A` OR `B` with FK cascade constraint?
I'm assuming this is not possible with PK and FK...
Let's say I have tables A, B, and C.
A and B are "parents", and B and C are "children"
A is the root
B can be the child of either A or B
C ...
1
vote
0answers
56 views
Scalable way to manage aggregated statistics over time in hierarchy?
I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value ...
-2
votes
2answers
78 views
What's the best way to represent an event from a system in a chat?
Conversations
--------------
id
Messages
--------------
id
conversation_id
user_id
reply_to_id
content
created_at
I was looking at this question and I had a question: How to build a table for a ...
1
vote
0answers
50 views
Nested set model in higher dimensions
The nested set structure is well-known and involves using left and right values to denote the relative heirarchy of nodes on one axis.
Is there a generalization of this structure to higher dimensions?...
6
votes
3answers
6k views
Parent/Child Relationship Table Design - What's The Best Practice?
I have a single table for storing 'Tasks'. A task can be a parent and/or a child.
I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have ...
2
votes
1answer
95 views
Correct way to join row in parent table to row in one of several children tables
I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will ...
1
vote
1answer
29 views
How to add foreign key in a table where column value can come from two different tables?
I have three tables
create table main_comments (
id serial primary key,
comment text not null
);
create table sub_comments (
id serial primary key,
main_id integer not null,
comment ...
3
votes
2answers
203 views
Get all descendants from 3 hierarchical tables
I have the following tables within a database:
+-------------+ +-------------+-------------+ +---------------------------+
| project1 | | project2 + | project3 ...
0
votes
1answer
82 views
Bypass truncated data type error in `CREATE TABLE AS`
I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list ...
3
votes
2answers
41 views
Nullable, dependent attributes
I'm designing a database for students. Computer science students here in Switzerland can decide between different specializations, e.g. application development, system engineering etc.
There are ...
5
votes
1answer
918 views
Condition in WHERE vs condition in CONNECT BY
Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.
select concept.concept_id, concept.PARENT_ID
from ...
3
votes
1answer
69 views
How to store hierarchical dimension for timeseries data
I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on.
As part of the search the user can select one or more Categories ...
1
vote
1answer
52 views
Select top-most folder in a hierarchy by given criteria in MySQL
I'm using MySQL (5.7.20) to store a folder hierarchy. Each folder is stored together with all direct and indirect parents including the distance. As an example, the following folders
A
+- B
+ C
...
0
votes
1answer
100 views
Procedure for tree view
I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.
Condition: every parent has two child.
DROP ...
5
votes
2answers
16k views
Parent Child Relation in same Table
I have a table storing parent/child records as such:
+-------+------------+---------+---------+------------+-----------+
|custid | custname | deptid | company |parentcustid| enrolled |
+=======+==...
0
votes
1answer
190 views
Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB
I am very new to database design. I am hoping for suggestion or solutions for a feature I am working.
Note: We are majorly using JSONB type in Postgres
Feature:
I have a orders table which would ...
3
votes
1answer
243 views
Is a Self-defining Table of Domains Normalized?
I don't really know the official name of what I've made but it can't be the first time this has been made, so apologizes up front for not knowing every term.
My question, is a table as outlined below ...
0
votes
1answer
129 views
Why does Oracle yield multiple levels per row in this hierarchical query?
I'm trying to reproduce the first simple example from this tutorial.
However, I don't understand why I get so many rows back. I would expect that 1 reports to 2 only at level 1 and not at level 2 as ...
2
votes
2answers
4k views
How to organize binary tree in database? [closed]
Here is the situation, I need to organize some users like an binary tree to explain organization workflow, but organization can change.
Here is how its look like:
1
/ \
2 ...
2
votes
0answers
360 views
Auto-update hierarchyid of multiple records in INSTEAD OF UPDATE trigger
I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run ...
0
votes
1answer
171 views
Recursive Seller Account with Balance
I need to build a database for managing Short Messaging Service (SMS).
In this I have 2 types of users
Reseller
Must have starting SMS balance for example: 1,000 - credit table
He can sell ...
3
votes
2answers
2k views
Get Nested/Hierarchical Data from SQL Table
I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'.
The TagAssignment table stores the relationship between a Tag and a date. So ...
0
votes
1answer
662 views
While Loop - Parent/Child Tree
I'm trying to recursively loop through and return all child_id's that have the root element of 9.
The structure:
->9
->->8
->->->17
->->22
->->->11
Parent Child ...
0
votes
1answer
86 views
How to retrieve all the rows where primary key value found in the other column in the row in MySql
Following is MySql Table in the snapshot:
column sno could be found in the other rows in column p_sno. i.e.
sno = 5 linked through p_sno = 5 with sno = 6 (two rows)
sno = 8 linked through ...
5
votes
2answers
194 views
Find missing folders paths in split hierarchies
I have a table which contains folders' paths. This table contains four columns:
DirID - The folder's ID.
BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the ...
2
votes
2answers
1k views
How to flatten and pivot a normalised hierarchy table
I have an OrgChart table that looks something like this:
PositionCode PositionName ParentCode Level
AA0001 CEO 0
AA0002 CFO AA0001 1
AA0003 ...
-1
votes
3answers
632 views
Create parent node and parent id [closed]
I have a table which contains id, nodeid, nodevalue, and nodelevel columns.
Now I want to create new temp column ParentnodeId.
I tried using CTE but not getting the correct result.
Id Nodeid ...
2
votes
1answer
501 views
Unflatten hierarchical data in SQL Server
I have some tables in the staging area of a data warehouse that I'm filling with data from some flat, comma-delimited text extracts from another system. When the data comes in the hierarchy of parents ...
1
vote
1answer
65 views
Which postgres design for a nested resource that can link to the same type of resource
I have this kind of data that I need to store in postgres (ruby object).
#<InitRes:0x007f8ec3734c78
@kind="RestrictionsSchema",
@restrictions=
[#<ParsedRes:0x007f8ec60ed218
@...
1
vote
0answers
987 views
Joining recursive table in PostgreSQL
Situation
I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" ...
0
votes
1answer
179 views
SQL relationship for nested groups
I'm working on a creating a database schema for a legacy app that currently saves its data to XML files.
I'm having trouble representing this relationship:
Task Group 1 contains
|-- Simple Task 1
|--...
8
votes
2answers
11k 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
0answers
99 views
SQL select for a specific node all subnodes
I'm using mySQL on phpMyAdmin, my table is
| code | name | parent |
|------|------|--------|
| 10 |node10| 5 |
| 5 |node05| 2 |
where 5 is the parent of 10 (which is also a node), I ...
0
votes
1answer
128 views
Join mysql tables to find siblings and get their notifications
i have two mysql tabes one for company and one for notifications.
Company
+-----------------------+
| ID |parentID|vaue |
|1 |0 |22020 |
|2 |1 |23423 |
|3 |2 |22222 ...
1
vote
1answer
405 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,
...
