Skip to main content
11 votes
Accepted

Hierarchical permissions in a table stored hierarchy

Using this model, I have come up with a way to query the Pages table in the following manner: SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, @...
Andriy M's user avatar
  • 23.1k
10 votes

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

Hierarchic Self-Referential Data Syntax shown using the free and open source database, PostgreSQL You need a self-referential table, which creates hierarchy in the database. This is how it looks. ...
Evan Carroll's user avatar
  • 63.9k
10 votes

Transposing hierachical data from one VARCHAR to two INTs

As your data looks a bit like hierarchyId I thought about using that. Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped. ...
wBob's user avatar
  • 10.4k
8 votes
Accepted

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

Add a "path" field and sort by that similar to a file path. As ypercube mentioned, the sorting is overly simplistic in this example and just happens to work but for simplicity's sake I'll leave as is. ...
Ben Campbell's user avatar
7 votes

Parent Child Relation in same Table

A common way to do this in SQL Server is to use a Recursive CTE: Here's your test data as an insert: CREATE TABLE #yourmom ( custid INT, custname VARCHAR(10), deptid VARCHAR(3), ...
Erik Darling's user avatar
  • 42.1k
7 votes

Parent/Child Relationship Table Design - What's The Best Practice?

The technique you are describing for representing task hierarchy is called 'Adjacency list'. Although it is the most intuitive to humans, it doesn't lend itself to very efficient querying in SQL. ...
SQLRaptor's user avatar
  • 4,098
5 votes

Transposing hierachical data from one VARCHAR to two INTs

Add a column to dbo.Almoxarifado2 that will hold the original value of idAlmoxarifado. Add all rows from dbo.Almoxarifado and then use idAlmoxarifadoOrg to update idAlmoxPai with the value of ...
Mikael Eriksson's user avatar
5 votes
Accepted

Find missing folders paths in split hierarchies

I'd try something like this: First, create a new table and copy the existing data there. Then iterate, and in each iteration, remove the last node of each filepath (e.g.: 'A\B\C\D\E' becoming 'A\B\C\...
ypercubeᵀᴹ's user avatar
5 votes
Accepted

Parent Child Relation in same Table

If you have only one level of children, you could join the tables SELECT a.custid, a.custname, a.deptid, a.company, b.custid AS bcustid, b.custname AS bcustname, b.deptid AS bdeptid, b....
Olivier Jacot-Descombes's user avatar
5 votes
Accepted

Condition in WHERE vs condition in CONNECT BY

After reading the documentation, I came to the following conclusions. One difference between the two queries is that the first query has two condition in the connect by , in order to identify a ...
giannis christofakis's user avatar
4 votes
Accepted

Create a hierarchy chain (reverse path) for each node in a tree

Looks like a GROUP_CONCAT to me, unless I'm missing something, eg CREATE TABLE #heredity ( parent INT NOT NULL, child INT NOT NULL, depth INT NOT NULL, CONSTRAINT _pk_heredity ...
wBob's user avatar
  • 10.4k
4 votes

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

Cheating, just a bit ;) Look ma, no recursion! Tested at rextester.com SELECT btree.* -- , a,b,c,d -- uncomment to see the parts FROM btree OUTER APPLY ( SELECT rlc = REVERSE(LEFT(...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Social network comments and posts model in PostgreSQL 9.6

First the author table, that's easy. CREATE TABLE author ( authorid serial PRIMARY KEY, username text UNIQUE ); Now we use a self-referential post table to create the hierarchical ...
Evan Carroll's user avatar
  • 63.9k
4 votes
Accepted

Is a Self-defining Table of Domains Normalized?

This is a called a "self-referencing table", or "single-table hierarchy". You normally query them with a recursive CTE because you want the children, not just on a single conditional. Note, normally ...
Evan Carroll's user avatar
  • 63.9k
4 votes

How make recursive json on mysql

MySQL 8+ Using MySQL 8, you can write a Recursive Common Table Expression. Recursive Common Table Expression Examples The example under Hierarchical Data Traversal is pretty much what you're ...
Evan Carroll's user avatar
  • 63.9k
4 votes
Accepted

How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

Snowflake currently supports recursive CTEs. I just build an sql to parse an account tree to consolidate amounts at the branch level. From the source account tree data, main is used to build each ...
Sairam Asapu's user avatar
4 votes

System/database design for comments/replies and upvotes at scale

This question is likely to generate a lot of opinions. In comes the "NoSQL" crowd touting in vague terms why relational is bad and this and that. The issue is complicated because the ...
Gunther Schadow's user avatar
3 votes
Accepted

MySQL parent / child table not showing parent entries

The join should be on the opposite direction and without the WHERE. Think of this as starting with any category (r). All of them either have a parent (so the parent_name and parent_id will appear) or ...
ypercubeᵀᴹ's user avatar
3 votes

How to represent directed graph with multiple parents?

Usually create a nodes table and a relationships table. Directed graphs are't really hierarchical and they can have loops which makes querying harder. But if you think of a DAG as being a ...
Chris Travers's user avatar
3 votes

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

You should use LEFT JOIN instead of INNER JOIN in case category doesn't have subcategory. SELECT `parent`.`id` , `parent`.`name` , COUNT(`child`.`id`) AS `hasChild` ...
Mr. K's user avatar
  • 31
3 votes
Accepted

Unflatten hierarchical data in SQL Server

You can use a cross apply and the table value constructor. select U.CostCentreCode, U.CostCentreDesc, C.ValidFromDate, C.ValidToDate, coalesce(U.ParentCostCentreCode, '') ...
Mikael Eriksson's user avatar
3 votes

Find missing folders paths in split hierarchies

As far as you need all intermediate folders, I'd suggest a CURSOR solution. CREATE TABLE #Gaps (BaseDirID int, Path VARCHAR(2048)); DECLARE @BaseDir int, @LastPath VARCHAR(2048), @DisplayPath ...
McNets's user avatar
  • 23.8k
3 votes
Accepted

Nullable, dependent attributes

I would model this using a single table for the Professions, and a related table for Specializations. USE tempdb; DROP TABLE IF EXISTS dbo.Users; DROP TABLE IF EXISTS dbo.Specializations; DROP TABLE ...
Hannah Vernon's user avatar
  • 70.2k
3 votes
Accepted

How to add foreign key in a table where column value can come from two different tables?

I would suggest you structure your comments like this, CREATE TABLE comments ( comment_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, parent_id int REFERENCES comments, comment text ...
Evan Carroll's user avatar
  • 63.9k
3 votes

Parent/Child Relationship Table Design - What's The Best Practice?

There is no rule of normalization that prohibits null values or would require storing the adjacency list in a separate table. Both approaches are common, and there are not significant performance ...
David Browne - Microsoft's user avatar
3 votes

Parent/Child Relationship Table Design - What's The Best Practice?

From SQL Server 2017 and Azure SQL DB you can use the new graph database capabilities and the new MATCH clause to model this type of relationship. Look no nulls! A sample script: USE tempdb GO IF ...
wBob's user avatar
  • 10.4k
3 votes
Accepted

How to select id of father from the child id recursively

That is not Oracle syntax. Below is Oracle syntax: drop table menu purge; CREATE TABLE menu ( id INT PRIMARY KEY , name NVARCHAR2(40) , parent_id INT REFERENCES menu (id) ); INSERT INTO menu ...
Balazs Papp's user avatar
  • 40.8k
3 votes

CTE get all descendants with parents

Please try and let me know if it helps : WITH descendants as ( SELECT i.id, i.parent_id, CAST(i.id AS varchar) as Level FROM item i WHERE i.parent_id is null UNION ALL SELECT i1.id, i1....
Ergest Basha's user avatar
  • 4,025
3 votes

CTE get all descendants with parents

For definite base id use WITH RECURSIVE descendants(id, parent_id, most_parent_id) AS ( SELECT item.id, item.parent_id, item.parent_id FROM item WHERE item.parent_id = 1 -- given base id ...
Akina's user avatar
  • 20.1k
2 votes

Traversing tree-like data in a relational database using SQL

The standard SQL method is a “Recursive query” which is provided by the Recursive CTE and designated as WITH [ RECURSIVE ] in the query. The implementation isn't specified in the spec, only the ...
Evan Carroll's user avatar
  • 63.9k

Only top scored, non community-wiki answers of a minimum length are eligible