For issues relating to arranging items into ordered levels.
1
vote
1answer
64 views
Using Oracle hierarchical queries for transposing field value into rows
One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm ...
0
votes
0answers
69 views
Get the count of items related to nested category in sql [closed]
I have two tables: categories and items. i have stored categories using nested set structure. Categories have items. Items can be only added to leaf nodes of a root category.
For eg:
Categories
...
0
votes
0answers
62 views
Prefix traverse of a tree
I have a table with tree data as it defined in this question and have to implement traversal of the tree that looks like preorder traversal but instead of root --> left subtree --> right subtree ...
3
votes
1answer
116 views
I need help using hierarchyid datatype and requiring a unique constraint on a varchar field
I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had.
CREATE ...
4
votes
1answer
121 views
Modelling Hierarchical attributes
I am trying to figure out the data model for a retailer.
The retailer has several stores across the country and they are modeled using the following hierarchy:
Channel -> Zone -> City -> ...
2
votes
2answers
120 views
Hierarchy query with 3 tables
I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where:
Table1 (ID_table1, name_c, size)
Table2 (ID_table2, ID_table1, name_l, ...
4
votes
1answer
125 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 ...
-1
votes
2answers
119 views
Information sources for multiple hierachy trees in a single table
I need some quality sources - books, websites etc. - to educate myself about putting multiple hierarchy trees in a single table using SQL.
I'm looking for some good theoretical and practical ...
2
votes
1answer
324 views
Foreign key with multiple table options
I'm not sure if the title is very good, feel free to suggest a better one.
People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, ...
7
votes
3answers
665 views
Oracle: How do I query a Hierarchical table?
Background
This is for the construction of some views we'll be using for reporting.
I have an table of locations, the key fields being "location" and "parent".
The structure that these two fields ...
1
vote
3answers
370 views
MySQL function to return breadcrumb-like string from hierarchical structure
Show create table:
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
...
3
votes
3answers
417 views
Faster alternative to scalar UDF with recursion?
I have a scalar function that traverses a simple parent-child hierarchy of customers to find the ancestor that's in charge of billing. Here's a simplified version of the schema.
CREATE TABLE Customer ...
9
votes
2answers
181 views
Is there a set based way to load/read a tree branch with HierarchyId
I am playing around with HierarchyId, and I have not figured out a set based way to do the following:
insert all subtree at once
retrieve all subtree at once
This question is related to my ...
11
votes
3answers
1k 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 ...
7
votes
6answers
1k 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 ...
2
votes
1answer
267 views
Displaying parent attributes alongside child attributes
SSAS - Parent Child hierarchy - displaying other Parent attributes alongside Child Attributes
For example:
Employee Table
+-----+-------+------+---------------+
| KEY | NAME | ROLE | MANAGERKEY ...
3
votes
1answer
280 views
Where to place junction table?
I'm designing a database, in SQL Server 2005, and one of the tables stores 'risk assessments'. This 'risk assessments', besides the description, refers some risks, which are stored on other tables ...
5
votes
1answer
161 views
How can I efficiently represent inheritance down a tree?
I have a database with three "levels" of objects: A, B, and C. Table A has around 100000 rows, B has 500000, and C has 2 million.
In a fit of stupidity, I designed the database to use joining tables ...
1
vote
2answers
414 views
Oracle hierarchical query question (start with … connect by … )
From the Oracle documentation:
To find the children of a parent row, Oracle evaluates the PRIOR
expression of the CONNECT BY condition for the parent row and the
other expression for each row ...
14
votes
2answers
5k 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 ...
1
vote
2answers
995 views
hierarchical query in oracle
Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume ...
7
votes
3answers
440 views
Data Warehouse Creation
I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me.
Situation
We have a Cobol "database" (I ...
15
votes
3answers
2k 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 ...
