Tag: recursive-query

Postgres recursive query – get path to root given a leaf node

common-table-expression postgresql recursive-query sql

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

Mysql Mariadb recursive query

mysql recursive-query sql

I have the following table : and a recursive query on it : which works and prints the following result : I would have liked to add an extra column to the result, namely “parent_name”. How do I do this? Edited to add: https://stackoverflow.com/a/22376973/2027239 That was the answer on an older question which I used to build my query Answer

Recursive CTE in Postgres

common-table-expression hierarchical-data postgresql recursive-query sql

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

Managing multiple categories trees, using Python and PostgreSQL

postgresql python python-3.x recursive-query sql

I have multiple categories, which can have None or one or multiple sub-categories. The process theoretically can go to infinite. So, it is like having multiple trees. Tree example. I have also Item(s). An Item can be in multiple categories. At this moment to connect the categories, in database I use three fields: children (the children of a category), path([1,4,8],

Get all days in a month excluding weekends postgresql

date-arithmetic datetime postgresql recursive-query sql

I want to write a query sql for postgresql that can basically return me all days of a month excluding weekends. For example (For 11/2019) : First Week: 11/1 Second Week : 11/4 -> 11/8 Third Week : 11/11 -> 11/15 Fouth Week : 11/18 -> 11/22 Fifth Week : 11/25 -> 11/29 I can’t find any postgresql request that

Recursively getting a root ID of tree in Postgres table

postgresql recursive-query sql

I have a Postgres table users (E_8_User) which contains User_id as a primary key, Boss as a foreign key to the same table (it is not nullable, if some user doesn’t have boss, its Boss attribute = …

Create a calendar database table like this

date-arithmetic datetime mysql recursive-query sql

I would like to create a calendar SQL table like this one but for some years. (I’m using mysql 5.7.28) Date is DD-MM-YYYY Is it possible? Answer If you are running MySQL 8.0, you can use a recursive query: The recursive cte generates a list of datetimes between the given boundaries (here, that’s year 2019), with a 1 hour increment.

Mysql 8 How to use an if inside a stored procedure and using cte

database mysql recursive-query sql where-clause

I am using a CTE inside a stored procedure, and it works ok, but the problem is when I try to add a conditional, I got the following message: You have an error in your sql .. Syntax to use near ‘IF. Answer You cannot use an IF statement in a query. It is meant to execute a block of

Recursive Iterator for a single table

java jpa recursive-query sql

I have a table in which the foreign key refers to the id of the same table. I need to find the children of the first element that pass me as a parameter, until I reach a certain level. I need to find …

Get query result 15 second interval in Oracle

datetime oracle recursive-query sql sum

I have following query to get 15 seconds interval. However I want to show also ‘0’ values even if the result doesn’t have value. Here is the Query; select to_char(trunc(sample_time,’hh24′) + (trunc(…