All geek questions in one place

Using CONNECT BY for all parents and one child in a hierarchy through an SQL query in Oracle

I looked through some previous posts about using CONNECT BY. I need to find what to do if I want all parents (i.e. to the root) and only one child for node, say 4.

It looks like I will have to use a combination of the following two: -

SELECT * FROM hierarchy START WITH id = 4 CONNECT BY id = PRIOR parent union SELECT * FROM hierarchy WHERE LEVEL =<2 START WITH id = 4 CONNECT BY parent = PRIOR id

Is there a better way to do this, some kind of workaround that is more optimized?

+4
source share
1 answer

You must do this using the subselect (and DISTINCT ) to find all 4 children:

 Select Distinct * From hierarchy Start With id In ( Select id From hierarchy Where parent = 4 ) Connect By id = Prior parent

Using UNION , you can at least remove CONNECT BY from your second query:

  Select * From hierarchy Start With id = 4 Connect By id = Prior parent Union Select * From hierarchy Where parent = 4

Never use SELECT * , always name the columns that you really need. This makes it easy to read and optimize your query.

+8
source

Source: https://habr.com/ru/post/1305642/

More articles:


All Articles