All Questions
36 questions
1
vote
0
answers
126
views
Mysql8 Unable to order the result of WITH RECURSIVE query
I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
- 121
0
votes
1
answer
821
views
MySQL Select query for a calculated column that uses previous row value for current row
I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.
Table has 3 columns - Employee, Month, Salary. ...
- 1
3
votes
1
answer
318
views
String Manipulation of the Result from Recursive CTE
Good afternoon everyone
I found just one post here within the last year about this, but it doesn't help my situation.
I have been working with MySQL and trying to improve my knowledge of recursive CTE....
- 33
0
votes
1
answer
43
views
Retreive upper heirarchy
I have Categories stored in a single table.
id name parentId path
A1 Cat 1 0 Cat 1
A2 Cat 2 A1 Cat 1 > Cat 2
A3 Cat 3 A2 Cat 1 > Cat 2 > Cat 3
...
- 103
0
votes
1
answer
1k
views
How can I handle cycles / infinite loops in recursive CTEs in MySQL
Cycles in graphs lead to infinite loops in CTEs.
Dealing with them in Postgres is straightforward.
Since 8.0 MySQL also allows for CTEs. How I can detect cycles and infinite loops in MySQL CTEs?
The ...
- 823
0
votes
1
answer
278
views
Retrieve all linked categories
I have Categories stored in a single table.
Where there is no limit on number of childerns.
I want to fetch all the linked categories for the provided category id:
The reason for getting the hierarchy ...
- 103
0
votes
0
answers
214
views
Mysql parent child to pivot table
I want to convert my parent child table to pivot,below is my query
CREATE TABLE parent_child
with recursive cte (id, name, parentid,level) as (
select id,
name,
...
0
votes
1
answer
536
views
Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?
I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
- 1,888
3
votes
2
answers
5k
views
Recursive query on a self-referential table where each node has one link to its child's node
Consider the following table and data (fiddle available here):
CREATE TABLE test
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
next_id INTEGER NOT NULL
);
INSERT INTO test (id, next_id) ...
- 163
0
votes
2
answers
2k
views
Update rows based on a recursive CTE query result
This query shows all lines recursively based on a uuid / parentUUID relationship:
WITH RECURSIVE files_paths (id, parent) AS
(
SELECT uuid, parentuuid
FROM core_data
WHERE uuid = '2c828bbb-...
4
votes
2
answers
8k
views
Get the highest parent id recursively in MySQL
I want to find the uppermost parent ID for each entry. For example,
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
- 4,531
0
votes
0
answers
31
views
Fastest way of updating a spider web of data
I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
- 1
1
vote
2
answers
3k
views
Update Column from Recursive CTE (MySQL/MariaDB) [closed]
I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table.
My code is below- ...
- 13
2
votes
2
answers
1k
views
How to do a recursive sum of children in mysql?
I've searched and tried for long hours, and can't seem to get what I need.
I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT ...
- 165
0
votes
0
answers
73
views
SELECT query to read recursive relationship table
I have below MySQL database table which has a recursive relationship. A hospital maybe managed by another main hospital in this scenario. I have a difficult time trying to retrieve all data with name ...
- 23
0
votes
2
answers
2k
views
Limiting Common Table Recursion
I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.
SELECT id,MK, pid,AFI
FROM M A
WHERE LEVEL <= 2 -- limiting iterations ...
2
votes
1
answer
3k
views
How to query all entities associated with all children (recursive) of a node in SQL?
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.
Ok, I’m having trouble putting this into words… so bear with me.
Say I have a tree of ...
- 143
1
vote
1
answer
504
views
Rewrite SQL query in CTE query
I have this table which I would like to store a chain of records.
CREATE TABLE table_name (
id INT,
unique_id varchar,
reference_id varchar,
);
I want to implement SQL query for MariDB ...
- 115
2
votes
1
answer
2k
views
How make recursive json on mysql
my table looks like
CREATE TABLE `test_comments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned DEFAULT NULL,
`test_id` int(10) unsigned NOT NULL,
`user_id` int(...
- 21
1
vote
1
answer
2k
views
How to select recursively in a child parent design situation (in MySQL)?
Let's take into consideration the following tables:
CREATE TABLE actions
(
id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
user_id BIGINT(20) ...
- 293
1
vote
0
answers
561
views
How to get the hierarchy of particular employee using JPA spring boot?
I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot.
The problem is similar to the following link
Similar Data Model
If A reports to B and B reports ...
- 11
1
vote
1
answer
2k
views
Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?
Not sure if this is a generic question, but unless not, this is MySQL 5.6.
We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be ...
- 21
0
votes
1
answer
373
views
Recursive Seller Account with Balance
I need to build a database for managing Short Messaging Service (SMS).
In this I have 2 types of users
Reseller
Must have starting SMS balance for example: 1,000 - credit table
He can sell ...
- 101
0
votes
2
answers
2k
views
While Loop - Parent/Child Tree
I'm trying to recursively loop through and return all child_id's that have the root element of 9.
The structure:
->9
->->8
->->->17
->->22
->->->11
Parent Child ...
- 101
0
votes
1
answer
12k
views
Recurstive query parent child speed
Recursive queries to find the tree-structure (parent-child) in a table are commonly found here, however most have the problem that the childID must be higher than the parentID.
A possible solution ...
- 167
2
votes
1
answer
28
views
need to correct this Query/Loop to find and start "new branch" in the output
given these tables:
PERSONS
person_id fname lname info
1 LB Johnson "info..."
2 Bobby Baker (null)
3 Malcolm Wallace (null)
4 Billy ...
- 81
3
votes
1
answer
98
views
Hierarchical Storage and Query
I am editing and providing new info to this question because i've found what I need, just have a couple of questions about efficiency.
Now, I have this table:
category
--------------------------
...
- 81
4
votes
3
answers
2k
views
Best way to design / query multi-level relationship table
My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database ...
- 41
0
votes
2
answers
859
views
COUNTING results from self-referenced table recursively
I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.
Sample:
id | user | idFather | createdAt
---+---------+----------+-----------
01 | ...
- 241
1
vote
1
answer
2k
views
mysql query parent child relation get childs
I have a user table in mysql. per user has got a parentuserID in the same table. i want to crate a function that give me the all child users when i give the UserID. my table structure is below.
DROP ...
-2
votes
1
answer
342
views
MySQL - GROUP BY clause recursively? [closed]
Under MySQL I can not group the three separate data columns via GROUP BY clause.
This sample contains four brands, each brand has 2 models and 4 versions of engines. I need to extract 4 vehicles the ...
- 29
0
votes
2
answers
3k
views
Single query for retrieving a category path
I have the following table structure and content:
+-------------+-----------+------------+
| category_id | parent_id | name |
+-------------+-----------+------------+
| 1 | 0 |...
- 101
2
votes
2
answers
7k
views
How can I count all children & grandchildren until the 7th level?
I have 3 tables right now
1. members
2. members_parents
3. members_status
How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = ...
- 21
0
votes
2
answers
238
views
Hierarcy Query Mysql [duplicate]
I have a table like this...
==========================================================================
UID || PARENT ID || SPONSOR ID
===============================================...
- 101
0
votes
1
answer
16k
views
Fetch all parents from child id
I created a table with name "vegs" and inserted values just like this.
id name parent
--------------------------------------
1 fruits 0
2 Apple 1
3 ...
- 29
2
votes
1
answer
14k
views
Recursive Query in MySQL using stored proceedure and CURSOR
I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships.
For simplicity sake, lets say users ...
- 506