Hierarchy query with 3 tables

hierarchyoracle

I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where:

Table1 (ID_table1, name_c, size)

Table2 (ID_table2, ID_table1, name_l, size)

Table3 (ID_table3, ID_table1, ID_table2, name_d, size)

How do I make a hierarchy with all this information? I've looked for it but only found solutions with information into a single table.

*EDIT: I edited the information in the tables because they do not express myself

Best Answer

I've never tried anything like that, but what if you UNION your three tables into one, and then do a "one-table hierarchical query" on the result of that?

WTIH alltabs as
    (Select 'T1' as src, Table1.ID, NULL AS ID_table1, NULL as ID_table2, Table1.name, Table1.size
    from Table1
    union
    Select 'T2' as src, Table2.ID, Table2.ID_table1, NULL as ID_table2, Table2.name, Table2.size
    from Table2
    union
    Select 'T3' as src, Table3.ID, Table3.ID_table1, Table3.ID_table2, Table3.name, Table3.size
    from Table3)
Select *
from alltabs
/*you can have an optional STARTING WITH clause here, but it's not clear from your question what that would be*/
connect by (id = id_table1 and (src = 'T1' or src = 'T2'))
        or (id = id_table2 AND (src = 'T2' or src = 'T3'))

(I'm assuming that the relationship is Table1 PARENT OF Table2 PARENT OF Table3).

Related Solutions

Select fields from multiple tables

You should be able to get the result that you want by using a LEFT JOIN on each of the tables.

select a."text_area",
  c."id_cor",
  c."text_cor",
  d."type"
from area a
left join color c
  on a."id_area" = c."id_area"
left join atv t
  on c."id_cor" = t."id_cor"
  and c."id_uo" = t."id_uo"
left join dest d
  on t."id_dest" = d."id_dest"
where t."id_uo" = 1222
order by c."id_cor"

See SQL Fiddle with Demo

Result:

| TEXT_AREA | ID_COR | TEXT_COR | TYPE |
----------------------------------------
|     info1 |      1 |   create |    M |
|     info1 |      2 |    sssss |    E |

You will see that when I used the LEFT JOIN on the atv table, I joined it to the color table on both id_cor and id_uo.

Connect by prior – multiple fields and query efficiency

Here are some additional questions that would help me answer this question better: (1) how often is this query executed; (2) does the result set have to be returned as a single row per ID; (3) in the actual environment can one item have more than 3 levels of depth?

I am going to assume that data sample is not representative of your actual environment and that the depth of this hierarchy may go deeper than 3 levels. In order for a single-row query to be successful in each case there must be a lot of potentially redundant SQL to account for all possible levels of depth. If your query accounts for an item having up to 5 levels of depth, but only one of your items has that many levels then you are putting unnecessary load on the database and potentially face performance delays with long running queries. A multi-row query could get much better performance, but that probably isn’t how you need the data returned (like below).

SELECT H.* FROM HIERARCHY_TBL H START WITH H.EQUIP_ID = '1'
CONNECT BY PRIOR H.PARENT_EQUIP_ID = H.EQUIP_ID

If this is an ad hoc query occurring infrequently then dealing with performance delay may be an acceptable option so you could stick with what you have. If this query is executed very frequently then performance delay is going to be unacceptable by the users.

As far as suggestions, if this SQL is being executed from within an application then you could look into using dynamic SQL. You could also check into creating a SQL profile to see if that would improve performance. You could also create a view that does most of this leg work for you and then hit that view with your query. The view option won’t reduce execution time, but it may help with clarity. If the data in these tables doesn’t change very often you could also look into using a materialized view that is refreshed from time to time – this will give you a lot of performance gain, but materialized views have their own challenges and depending on the environment some DBAs may discourage their usage.

I hope this helps.

Best Answer

Related Solutions

Select fields from multiple tables

Connect by prior – multiple fields and query efficiency

Related Question