I have a self-referential table User: Note that there are circular references. I want to get all the followers, and followers of the followers, and so on of a user such that all the followers are presented in a flattened list, with their respective depths For Adam: Problem I want to avoid the rows 3 and 4, which represents two
Tag: common-table-expression
MySQL – Recursively list all parents and ancestors of all items in table
I have a table with a parent/child hierarchy that supports multiple (theoretically infinite) levels of nesting: I am trying to build a query that produces a concatenated list of every item’s parent items up until the highest parent in the tree: Based on a number of other answers here I have constructed the following MySQL query: See Fiddle here: http://sqlfiddle.com/#!9/48d276f/902/0
CASE condition and SUM() common table expressions or Group By
Hope some can help ? I have a 3rd party software were i can do custom sql querys but not able to change any of the database. Struggling to get the result i would like. Do I use GROUP BY or CTE to get the result i need? This give me the result below . I have removed Where c=1
Recursive query in Oracle until a parent in the hierarchy meets a condition?
I have a table like the following: And I’d like to be able to fetch each ID with its corresponding VALUE_ID. I want to do it in such a way that if a row has a VALUE_ID as NULL, it “inherits” the VALUE_ID of the first parent above it in the hierarchy, that has a VALUE_ID as NOT NULL. So
SQL: How to add string text to my CTE output
I’ve a SQL question. I want to add a text value to the front of the JSON result of my view. I am now able to display the results of my view as JSON and then return with the “SELECT * FROM @TABLE” as the result. I need this return query for my ETL process. The output of my CTE
Is branch pruning possible for recursive cte query
This is inspired by question Retrieve a list of lists in one SQL statement – I have come up with a solution, but I have doubts on its efficiency. To restate the problem: we have 2 Tables: Person and …
Postgres recursive query – get path to root given a leaf node
I have a leaf node in a tree structure and I need a Postgres query to recursively traverse the parent nodes to get me the full path to my leaf node. My table looks like this – the problem being there is no specific root node (i.e the top-most managers end up reporting to each other) Given a user such
Recursive CTE in Postgres
I have a table with the structure: And, for each employee, I need to show the top manager ID. I mean, if for example, I have the EmployeeID 2 whose manager is 3 and, therefore, the number 3 has the manager number 5, I would have to show: I need to do this with a Recursive CTE in Postgres. Answer
SQL append data to table using dense_rank()
I’ve been trying to piece together bits of code that I’ve googled but haven’t had any luck. I’m trying to add a new column to an existing table. I can view the column using the below code from a select query: I basically want to append the results from this select query to my original table. I’ve tried using a
How to flatten parent-child hierarchy from bottom up using CTE
for my first question on stack overflow I have the following problem, I’ve got an hierarchy table that looks as follows: The data looks as follows: the row with the wbe_node_type ‘leaf’ is the lowest level in the hierarchy, maximum depth is 8 levels deep. What I want is to flatten the hierarchy in one row from the bottom level