BiTel

SELECT mn.*, tp.title
FROM tariff_plan tp
join module_tariff_tree mtt on mtt.tree_id=tp.id
join mtree_node mn on mtt.id = mn.mtree_id
where tp.id=391

3517   586   763   service   4   155   xCity2
3518   3517   763   cost   col&1%costType&0%type&2%cost&0.0   1   xCity2
3519   3517   763   attr_set   set_id&25%override&0   2   xCity2
3667   586   763   tariff_config   session_detail.delayed.update=1log_session.delayed.update=1   171   xCity2
3514   0   764   root      508   xCity2
3515   3514   764   month_mode   sid&5%mode&month   1   xCity2
3516   3515   764   month_cost   type&0%cost&490.0   1   xCity2

import java.util.*;
import java.io.*;
import java.sql.*;

boolean walkTreeItems(Connection con,int tree_id, int mid, int deep)
{
   boolean result = false;
    int mtree = 0;
    int mtreeParent = 0;
 
    String query = "SELECT id, parent_tree FROM module_tariff_tree WHERE mid=? AND tree_id=?";
    PreparedStatement ps = this.con.prepareStatement(query);
    ps.setInt(1, mid);
    ps.setInt(2, tree_id);
    ResultSet rs = ps.executeQuery();
 
    if (rs.next())
   {
      mtree = rs.getInt(1);
      mtreeParent = rs.getInt(2);
 
      if (mtreeParent == 0)
      {
         ps.close();
          query = "SELECT parent_tree FROM tariff_tree WHERE id=?";
          ps = this.con.prepareStatement(query);
         ps.setInt(1, tree_id);
         rs = ps.executeQuery();
          if (rs.next())
         {
            mtreeParent = rs.getInt(1);
         }
 
      }
       ps.close();
       if (mtreeParent != 0)
      {
         walkTreeItems(con, mtreeParent, mid, deep + 1);
      }
 
      print ("subtree id="+String.valueOf(mtree)+" deep="+String.valueOf(deep));

      query = "SELECT id, type, parent_node, data FROM mtree_node WHERE mtree_id=? ORDER BY pos";
      ps = this.con.prepareStatement(query);
      ps.setInt(1, mtree);
      rs = ps.executeQuery();
      while (rs.next())
      {
         print(" item id="+rs.getString(1)+" type="+rs.getString(2)+" parent="+rs.getString(3)+" data="+rs.getString(4));
      }
       print("---");
      result = true;
   }
    ps.close();
   return result;
}

public void main( setup, con, conSlave )
{
   walkTreeItems(con,390, 1, 0);
}

subtree id=1 deep=2
 item id=1 type=root parent=0 data=
 item id=2 type=service parent=1 data=1
 item id=6 type=cost parent=2 data=cost&0.0%col&1%type&5
 item id=7 type=cost parent=3 data=cost&0.0%col&1%type&2
 item id=8 type=cost parent=4 data=cost&0.0%col&1%type&2
 item id=3 type=service parent=1 data=3
 item id=4 type=service parent=1 data=2
---
subtree id=75 deep=1
 item id=587 type=multi_service parent=586 data=sids&1,2,3
 item id=590 type=cost parent=587 data=type&2%cost&0.0%col&1
 item id=586 type=root parent=0 data=
---
subtree id=761 deep=0
 item id=3512 type=cost parent=3511 data=col&1%costType&0%type&2%cost&0.0
 item id=3513 type=attr_set parent=3511 data=set_id&24%override&0
 item id=3511 type=service parent=586 data=4
 item id=3666 type=tariff_config parent=586 data=session_detail.delayed.update=1
log_session.delayed.update=1

---