APEX: Tree Based on Multiple Tables

When you have a table with a hierarchical relation it is very easy to create a Tree component in APEX. Simply write the hierarchical query using START WITH and CONNECT BY and you're good to go. When you have a Master-Detail relation between two tables, and you want to show a Tree component you would have to make up the hierarchy yourself in order to use START WITH and CONNECT BY clause.
In this blogpost I will show you how to create the query which you can use to create a Tree component with multiple tables.
For this example I will use the EMP and DEPT table, which can be found in the SCOTT schema. Just to be clear on the relation between the two tables: DEPT is the master table with EMP as its detail table. The contents of the DEPT table is shown in the next image:

Note that there is an entry for department 40.
Next the content of EMP, note that there are no employees who work for department 40.

The column that implements the relation between the two tables is DEPTNO.
The next step is to create a result set with these two tables where a hierarchical relation is implied.

 select to_char(d.deptno) id
       ,to_char(null)     parent_id
       ,d.dname           name
   from dept d
 union all
 select e.deptno || '_' || e.empno
       ,to_char(e.deptno)
       ,e.ename
   from emp e

In the query above data from both tables is queried using a UNION ALL. The three columns that are selected represent the ID, the PARENT_ID and the NAME of the department or the employee. You can imagine that there is an implied relation between the ID and the PARENT_ID columns.
The first part of the query gets the data from the DEPT table. The DEPT table being the master table, there shouldn't be a PARENT_ID column with a value in it - hence the NULL.
The second part of the query has a "concatenated primary key"; the foreign key column (e.deptno) and the table's primary key (e.empno) are concatenated together using an underscore character. Of course you can choose whatever character you want. The PARENT_ID column is just the foreign key column (e.deptno)
It is not strictly necessary to use the TO_CHAR () function in the PARENT_ID column, but when you start adding more tables in your query you might concatenated multiple keys together to form the ID and possibly get an inconsistent datatype exception.
The resultset of the query is as follows:

In the resultset above you can see how the ID and PARENT_ID columns relate to each other.
The last step is to create the actual hierarchical query using START WITH and CONNECT BY:

select sys_connect_by_path (name, ',') scbp
  from (
   select to_char (d.deptno) id
         ,to_char (null)     parent_id
         ,d.dname            name
    from dept d
   union all
   select e.deptno||'_'||e.empno
         ,to_char (e.deptno)
         ,e.ename
     from emp e
   )
start with parent_id is null
connect by parent_id = prior id

The query that you would use in your APEX page could be like this:

select case
          when connect_by_isleaf = 1 then
           0
          when level = 1 then
           1
          else
           -1
       end as status
      ,level
      ,name as title
      ,null as icon
      ,id as value
      ,null as tooltip
      ,null as link 
  from (
   select to_char (d.deptno) id
         ,to_char (null)     parent_id
         ,d.dname            name
    from dept d
   union
   select e.deptno||'_'||e.empno
         ,to_char (e.deptno)
         ,e.ename
     from emp e
   )
start with parent_id is null
connect by parent_id = prior id

And to see the result of all this hard work, I put up a simple Demo in my demo application