selecting parent child from same table in mysql

Question

After searching a lot here, I could not find solution to my problem. So, I am posting this question.

I have a Database Table which has the structure like this:

folder_id    folder_name    parent_id
--------------------------------------
    1          Private          0
    2          Public           0
    3          Photos           0
    4          December         3
    5          Bday             4
    6          Celeb            5

In hierarchical form, it will be like a folder structure :

-- Private
-- Public
-- Photos
----- December
-------- Bday
----------- Celeb

Now, I would like to select a Path upto a particular folder, like to Bday or Celeb folder. Thus, I want a MySQL query which will return me only the rows containing the folders between the path to a specific folder.

For Example, If I want a path to Celeb folder, then the Query should return these rows only :

folder_id    folder_name    parent_id
--------------------------------------
    3          Photos           0
    4          December         3
    5          Bday             4
    6          Celeb            5

Currently, I am stuck with this query and I am not able to make it work. The Query I am currently trying :

SELECT f.*
FROM fd_folders f
LEFT JOIN fd_folders p
ON f.folder_id = p.parent_id
WHERE f.folder_id <=6
ORDER BY f.folder_id;

But, the problem is that it is also returning the two other folders, i.e, Private and Public.

Please help.

Thank You

Best Regards

i think you're going to need some sort of recursion. I'll see if I can find my old script if nobody else helps you by then — Russell Uhl, Jul 03 '13 at 12:22
to begin the query is wrong it should be: ON p.folder_id = f.parent_id — SQL.injection, Jul 03 '13 at 12:25
and to end you need to write a stored procedure to handle to execute the recursive query... — SQL.injection, Jul 03 '13 at 12:26
so my entire answer, which i just now figured out, was based off of the fact that you were using MSSQL...which you are not. If you want to see the recursive version (due to academic curiosity) let me know, but it will NOT work in mysql — Russell Uhl, Jul 03 '13 at 13:15
**@Russell Uhl:** Ok. But I am trying to ignore Recursive implementation for this, for saving memory. — Ankur Thakur, Jul 03 '13 at 13:42
you can look at [this answer on stackoverflow](http://stackoverflow.com/questions/36523356/get-all-data-of-parent-child-relation-ship-from-same-table-in-mysql). — Saleh Mosleh, Apr 11 '17 at 10:52

score 1 · Answer 1 · answered Jul 03 '13 at 14:02

I'll just post this even though it does not DIRECTLY answer your question.

It looks like you may have to set up your table to have hierarchical data: http://www.sitepoint.com/hierarchical-data-database-2/

This will take some playing with to get set up correctly, and inserting new data will not be a fun process. Your life may be much easier if you use an additional language that processes the results for you.

If you have the option of using SQL Server instead (not mysql), this whole thing actually becomes quite simple. You just need a Common Table Expression:

;WITH DirectoryTree (folderId, folderName, parentId)
AS
(

    SELECT d.folderId, d.folderName, d.parentId
    FROM zzz_Directories d
    WHERE d.folderId = 4 -- target folderId

    UNION ALL

    SELECT d.folderId, d.folderName, d.parentId
    FROM zzz_Directories d
    INNER JOIN DirectoryTree dt ON dt.parentId = d.folderId

)

SELECT dt.folderId, dt.folderName, dt.parentId
FROM DirectoryTree dt

(Semicolon intentional)

Regrettably, mysql doesn't support this. Instead, you may have to do what was suggested in the comments: use a stored procedure. I don't have an environment up to test this, but I imagine such a procedure would have two parts:

if my parent is 0, return me
if my parent is not 0, return me UNION the procedure

the procedure would simply recursively call itself until the parent = 0. I don't know if this is even possible, but it's a place to start.

selecting parent child from same table in mysql

1 Answers1