Retriving Hierarchical data using SQL

Question

suppose i have too table "folder" and "file"

let Folder have two column ID and FolderID

Folder
------
ID int primary  <-.
FolderId Int    --`

folderId is an internal relationship with Id column of folder table

File table have Two column ID and FolderId

File
-----
Id int primary
FolderID          --->Folder.ID

FolderId is having external relationship with Folder tables's ID column

**Folder**
Id      FolderId
---     --------
1       NULL
2       1
3       NULL
4       2
5       2

**file**
Id      FolderID
---     ---------
1        2
2        3
3        2
4        4
5        5  

Hope you got the situation,

As we know we can create many folder inside folder and inside folder (Recursive)

Now we want to get list of ALL files (id) which is inside folder id=2 and its Sub Folder (required file id is 3,4,5)

using Recursive function we can get this to work but how to do it more efficient in SQL

Answer 1

Have a look at the answer to this question. Connect By Prior Equivalent for MySQL

In other databases (such as Oracle) you can use a CONNECT BY in a SQL statement, but it seems that MySQL doesn't support recursion in selects.