0

I have two tables, structures of the same is as below:

Table 1. Transactional data table 

trx id.
1
2
3
4
5..etc

Table 2 table 2 has the parent child relationship as below .

id       subject_id (Child)                   object_id (Parent)
1         2                            1
2         3                            1
3         4                            1
4         5                            1

Now using above tables, the expected output is as follow:

1
2
3
4
5

Please let me know how can I achieve the same. I need to get the details from the Table 1 along with parent and its all children in the hierarchy. I just have one level of hierarchy.

Vjai
  • 11
  • 1
  • 4
  • You "sample data" is confusing me. Those are just number and it's not clear to me where those numbers come from and how they are computed in the final result. For all I see this could be a simple `select * from table1`. –  Nov 22 '15 at 17:57

2 Answers2

0

As you just have a one level hierarchy you can get it to work just ordering the results correctly. Try this one:

select obj.object_id, t.*
from
(
  select  
    object_id, 
    object_id as parent_id
  from table2
  union 
  select  
    subject_id as object_id, 
    object_id as parent_id 
  from table2
) obj
inner join table1 t 
  on t.id = obj.object_id
order by 
  obj.parent_id,
  case when obj.object_id = obj.parent_id then 0 else 1 end,
  obj.object_id

;

Felypp Oliveira
  • 2,147
  • 1
  • 17
  • 17
  • Is there any other way like using connect by ..etc to get the Details. another detail information my second table has just parent child relation where as the table 1 has all the transactional details. and using table two how i can derive all the information from table 1. – Vjai Nov 22 '15 at 17:34
  • "connect by" extracts hierarchical data in Oracle, but my approach does works as well for any database... if you need data from "table1" so a simple join will solve the problem. See the edition I've done in the query. – Felypp Oliveira Nov 22 '15 at 20:28
0

I light variation on the comment of the @a_horse...

select * from table1
where id in 
(select object_id from table2
union all
select subject_id from table2)
order by id;

as expected

1
2
3
4
5

If you want to constraint the parent, simple add WHERE predicates in the subquery.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53