For issues relating to arranging items into ordered levels.

learn more… | top users | synonyms

0
votes
1answer
31 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
21 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
24 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 ...
2
votes
1answer
641 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, ...
0
votes
0answers
86 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
105 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 ...
5
votes
1answer
74 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 ...
21
votes
2answers
14k 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 ...
0
votes
1answer
68 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
0answers
22 views

Cloning rows with minor changes

I am attempting to clone a row in the database, not that difficult. However the row has a reference to another row in the database that when inserted needs to use a different value, perhaps provided ...
1
vote
1answer
99 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 ...
0
votes
1answer
572 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
44 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
137 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 ...
0
votes
1answer
966 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
475 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 ...
6
votes
1answer
2k 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
204 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 ...
2
votes
1answer
283 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 ...
4
votes
1answer
201 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
167 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
133 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
229 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
158 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 ...
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
649 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
751 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
279 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 ...
12
votes
3answers
2k 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
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 ...
3
votes
1answer
378 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
184 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
542 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 ...
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
508 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 ...