mysql - SQL substring Issue ( Where substring(...) = (select....) ) -

i'm trying search dirname full path using queries.

  select        `file_name` `tbl_files` substr(`file_path`,          locate('/',`file_path`)+1,            (char_length(`file_path`) - locate('/',reverse(`file_path`)) - locate('/',`file_path`))) =             (select `source_path` `tbl_transcode_folder` `trancode_folder_id` = 1 ) 

but return me nothing. when replace (select source_path tbl_transcode_folder trancode_folder_id = 1 ) it's result mnt/hd/1 queries below , it's response want dont want in way.

 select            `file_name` `tbl_files` substr(`file_path`,              locate('/',`file_path`)+1,                (char_length(`file_path`) - locate('/',reverse(`file_path`)) - locate('/',`file_path`))) = `mnt/hd/1` 

right sub-query returns random row table need define relation between tbl_transcode_folder table, need define relation between outer query , sub-query make them correlated

select `file_name`  `tbl_files`  substr(`file_path`, locate('/',`file_path`)+1             ,(char_length(`file_path`) - locate('/',reverse(`file_path`))              - locate('/',`file_path`)))             =(select `source_path`               `tbl_transcode_folder`               `trancode_folder_id` = 1               , `tbl_files`.`commoncolumn` = `tbl_transcode_folder`.`commoncolumn`)