sql - Query Output as tree MySQL -

sql - Query Output as tree MySQL -

i have 2 tables in mysql database :

folder table

+----+----------+ | id | name | +----+----------+ | 1 | folder 1 | | 2 | folder 2 | | 3 | folder 3 | +----+----------+

and file table

+----+--------+-----------+---------+ | id | name | id_folder | ref | +----+--------+-----------+---------+ | 1 | file1 | 2 | ref133 | | 2 | file 2 | 3 | ref2044 | | 3 | file 3 | 3 | refa001 | +----+--------+-----------+---------+

i want create mysql query output result :

output:

+---------+---------+ | name | ref | +---------+---------+ | folder1 | | | folder2 | | | file1 | ref133 | | folder3 | | | file2 | ref2044 | | file3 | refa001 | +---------+---------+

ie, display folder first (father) , under each folder these files

using union , 3 different values order (the file number list file, folder number list folders, , isfile value create folder appears before files), query doing want:

select name, ref ((select name, '' ref, 0 isfile, id id_folder, id table1) union (select name, ref, 1 isfile, id_folder, id table2)) tmp order id_folder, isfile, id;

mysql sql tree