I need someone familiar with Recursive Queries in MySQL 8
- or -
Post a project like this794
£20(approx. $25)
- Posted:
- Proposals: 5
- Remote
- #3504495
- Awarded
Description
Experience Level: Entry
*** UPDATE This is a re-post as the last developer who accepted this project could not do it - please make sure you are able to do it before bidding for the job***
I have asked for help on stack exchange for this, but I keep getting referred to similar answers which don't in fact do the job. I need someone who knows what they are doing to help me fix this personally.
I have two tables, one for items, and one for connections, something like this - real life database and phpmyadmin can be provided for testing.
id | item_name item_id | connected_id
------------------------ ------------------------
1 | horse 5 | 4
2 | dog 4 | 3
3 | cat 3 | 2
4 | mouse 2 | 1
5 | cow
I can create a recursive query to return items 2,3,4 and 5 when I search for item "1" and I have worked out how to find the parent of each item.
WITH RECURSIVE solution AS
(SELECT id,item_name,id AS parent_id
FROM items
WHERE id = 1
UNION ALL
SELECT p.id,p.item_name,c.item_id AS parent_id
FROM items p
INNER JOIN connections c
ON p.id = c.connected_id
)
SELECT * FROM solution;
This produces the following output:
id | item_name | parent_id
------------------------------------------
2 | dog | 1
3 | cat | 2
4 | mouse | 3
5 | cow | 4
Ideally need a full path between the connections and cannot work out how to add it. The connections are just examples and could be anything. There can be multiple parents and children for each item so I want a path from each item to each other item that is connected to it to be saved with each row.
id | item_name | path
------------------------------------
2 | dog | 1
3 | cat | 1->2
4 | mouse | 1->2->3
5 | cow | 1->2->3->4
**** EDIT ****
I can even say with some certainty that it looks something like this:
WITH RECURSIVE ctename AS (
SELECT empno, ename,
ename AS path
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.path || ' -> ' || emp.ename
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
empno | ename | path
-------+-------+------------------------
7566 | JONES | JONES
7902 | FORD | JONES -> FORD
7369 | SMITH | JONES -> FORD -> SMITH
But I cannot seem to make things work with a separate connections table rather than fixed parent ids in the items table.
I have asked for help on stack exchange for this, but I keep getting referred to similar answers which don't in fact do the job. I need someone who knows what they are doing to help me fix this personally.
I have two tables, one for items, and one for connections, something like this - real life database and phpmyadmin can be provided for testing.
id | item_name item_id | connected_id
------------------------ ------------------------
1 | horse 5 | 4
2 | dog 4 | 3
3 | cat 3 | 2
4 | mouse 2 | 1
5 | cow
I can create a recursive query to return items 2,3,4 and 5 when I search for item "1" and I have worked out how to find the parent of each item.
WITH RECURSIVE solution AS
(SELECT id,item_name,id AS parent_id
FROM items
WHERE id = 1
UNION ALL
SELECT p.id,p.item_name,c.item_id AS parent_id
FROM items p
INNER JOIN connections c
ON p.id = c.connected_id
)
SELECT * FROM solution;
This produces the following output:
id | item_name | parent_id
------------------------------------------
2 | dog | 1
3 | cat | 2
4 | mouse | 3
5 | cow | 4
Ideally need a full path between the connections and cannot work out how to add it. The connections are just examples and could be anything. There can be multiple parents and children for each item so I want a path from each item to each other item that is connected to it to be saved with each row.
id | item_name | path
------------------------------------
2 | dog | 1
3 | cat | 1->2
4 | mouse | 1->2->3
5 | cow | 1->2->3->4
**** EDIT ****
I can even say with some certainty that it looks something like this:
WITH RECURSIVE ctename AS (
SELECT empno, ename,
ename AS path
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.path || ' -> ' || emp.ename
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
empno | ename | path
-------+-------+------------------------
7566 | JONES | JONES
7902 | FORD | JONES -> FORD
7369 | SMITH | JONES -> FORD -> SMITH
But I cannot seem to make things work with a separate connections table rather than fixed parent ids in the items table.
Dan J.
100% (31)Projects Completed
21
Freelancers worked with
16
Projects awarded
56%
Last project
4 Mar 2024
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies