mysql> select "Expected Result when using table" ;
+----------------------------------+
| Expected Result when using table |
+----------------------------------+
| Expected Result when using table |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select staff.staff_id, cugd.office_id
    -> from  tb_user_profile ugp
    ->     inner join tb_user_data cugd on (ugp.profile_id = cugd.profile_id)
    ->     inner join tb_staff staff on
    ->     ( staff.office_id IN ( SELECT office_id FROM tb_office_hierachy WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%'))  or (cugd.office_id IS NULL) )
    -> where ugp.profile_id = 2000003
    -> ;
+----------+-----------+
| staff_id | office_id |
+----------+-----------+
|  5000003 | 9999      |
+----------+-----------+
1 row in set (0.01 sec)

mysql> select "Unexpected Result when using view" ;
+-----------------------------------+
| Unexpected Result when using view |
+-----------------------------------+
| Unexpected Result when using view |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> select staff.staff_id, cugd.office_id
    -> from  tb_user_profile ugp
    ->     inner join tb_user_data cugd on (ugp.profile_id = cugd.profile_id)
    ->     inner join tb_staff staff on
    ->     ( staff.office_id IN ( SELECT office_id FROM vw_office_hierachy WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%'))  or (cugd.office_id IS NULL) )
    -> where ugp.profile_id = 2000003
    -> ;
+----------+-----------+
| staff_id | office_id |
+----------+-----------+
|  5000001 | 9999      |
|  5000002 | 9999      |
|  5000003 | 9999      |
+----------+-----------+
3 rows in set (0.00 sec)

mysql>
mysql>