For issues relating to arranging items into ordered levels.

learn more… | top users | synonyms

0
votes
1answer
31 views

single mysql query that show a particular parent and child value [duplicate]

I am facing problem to show a particular parent child value and under all child value as a relationship. I have created single table tree and three column id, child and parent. I created a tree ...
1
vote
0answers
58 views

How to represent directed graph with multiple parents?

http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table. I'd like to model a similar data structure, except that ...
0
votes
0answers
23 views

How to join 2 tables and traverse up hierarchy at the same time

Any help that can be given on a current problem I'm having in an Oracle environment would be greatly appreciated. I have 2 tables. One contains jobs to be performed on pieces of equipment, the other ...
1
vote
2answers
77 views

Getting root node for a given leafs IDs

I got the following tables: +-----------------+ | PRODUCTS | +-----------------+ | #product_id | | #company_id | +-----------------+ | | +---------------------+ ...
0
votes
1answer
85 views

queries for hierarchical data

This table is a questions and answers CMS and i want to know how data is fetched from these tables. +------+----------+-------+ | id | parentid | type | +------+----------+-------+ | 1 | 0 ...
0
votes
1answer
44 views

hierarchical retreival of records in postgresql

I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it. PRODUCT MASTER ...
1
vote
1answer
257 views

HierarchyID: Get all descendants for a list of parents

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I ...
1
vote
1answer
59 views

How to handle multiple hierarchies in a data model?

In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies. Example 1: ...
2
votes
1answer
42 views

adjecency list model vs nested sets

I've stumbled upon an article describing nested sets model for storing hierarchical data and was quite intrigued by the supposed performance boost. after implementing this in our database, i've found ...
0
votes
1answer
72 views

Oracle Hierarchical query: with two node attributes NodeId and NodeType

I have the following use case where I want to make use of hierarchical queries to get the desired result. In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is ...
0
votes
2answers
193 views

How can I limit a hierarchical query to the point where there are multiple children?

This question is related to my previous question about hierarchical queries. I have a hierarchical query in Oracle 11gR2 that returns something like this: - Level 1 - Level 2 - Level 3 - ...
0
votes
0answers
36 views

how to use joins in hierarchical queries

how to use joins in hierarchical queries my try SELECT cust_id AS child_id FROM customer c,cust_assoc_dnorm d on d.dsend_id=c.cust_id WHERE connect_by_isleaf=1 and ...
0
votes
1answer
32 views

Table join inheritance

I create db with Person, Client, Employee, Manager, Developer. I'm interested what is the best way to put the data in tables. In which cases should programmer unite Client in table hierarchy with ...
0
votes
1answer
47 views

Design for hierarchical embedded database

The goal is to construct a hierarchical database of Objects having the following format: TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.] TypeB -> ObjectB [values, contains ...
0
votes
0answers
159 views

SQL Server 2008 single-line PIVOT/UNPIVOT combo

I have a request to get information from a table with a stored procedure that shows all the necessary data for in one line. This table includes a hierarchyID, so I am trying to use the HID methods in ...
0
votes
1answer
656 views

HierarchyID--multiple root values in one table

Currently I am trying to work my way around the HierarchyID concept in order to convert a few tables for our programmers. I have the gist of it understood, but one of the cases I'm working on has me ...
6
votes
1answer
235 views

Hierarchical structure with limited number of records per user

I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem. Any user can refer only 3 other users at max. So for ...
0
votes
1answer
84 views

storing overrideable hierarchical settings in sql server

I've searched the internet and get to this question. It's exactly describe my needing, only difference is that i need to store that settings in sql server database. How can i implement that in SQL ...
0
votes
1answer
2k views

Reverse connect by prior level value for arbitrarily-deep hierarchy

Background Using a menu hierarchy to drive a login process for users. Users have the ability to set their preferred menu item. When they log in, if they have a preferred menu item set, the system ...
1
vote
0answers
47 views

A pre-existing hierarchial table as relational

Currently the database contains something like so (these are the main fields but there's a large amount more): +----------+----------------+----------+ | Agency | parent | main_term ...
1
vote
1answer
139 views

How do I get value from parent row

I have two tables. One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column ...
1
vote
1answer
215 views

Database schema for a variable-level hierarchy with variable types?

A client is asking for a database structure for a medical translation app that involves narrowing down a health issue and then asking specific questions on it. The tricky part of representing this in ...
1
vote
1answer
3k 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 ...
4
votes
2answers
2k 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 ...
10
votes
1answer
6k 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, ...
1
vote
1answer
441 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 ...
5
votes
1answer
309 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
223 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
144 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
319 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
191 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
798 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, ...
8
votes
3answers
2k 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
946 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
1k 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
374 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 ...
13
votes
3answers
3k 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 ...
9
votes
6answers
2k 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
297 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
471 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
194 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
823 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 ...
23
votes
3answers
22k 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
2k 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
544 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
3k 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 ...