0

I'm trying to understand this concept, but this example gives me a headache. I'd expect that it should behave somehow similarly like resursion/mathematical induction. On every step you have a current value and a rule based on which you can calculate the next value. So if you have a starting point start with ename = 'BLAKE' and connect by prior empno = mgr the resulting recursion should behave like the arrows i've painted on image, and not match every person to the starting point.
SQL QUERY

Thanks a lot

P.S. article i'm refering to

samkart
  • 6,007
  • 2
  • 14
  • 29
Pan Tomima
  • 1
  • 1
  • how does `emp` look like? – samkart May 23 '22 at 14:23
  • referred some docs. [this](https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm), [this](https://www.enterprisedb.com/postgres-tutorials/how-run-hierarchical-queries-oracle-and-postgresql), [this](https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-start-with-connect-by/) to understand its postgreSQL equivalents. Turns out it does not change how the table looks like. – samkart May 23 '22 at 14:29
  • but why all people refer directly to Blake Manager, why isn't it chained? – Pan Tomima May 23 '22 at 14:40
  • see `emp` table. Blake is indeed their manager according to the data. The hierarchical query did not update any records from the table - instead it creates a pseudocolumn `level` (along with other things). Check out the links I shared above. – samkart May 23 '22 at 14:44
  • so i can think about hierarchical queries as a BFS traversal of table with a root defined by `start with ...` and `connect by` as a way of defining which rows are a children node of parent = how edges connect verticies? – Pan Tomima May 23 '22 at 14:51
  • correct me if i'm wrong : if in the emp table there was a row which had a mgr=7499 (like empno of ALLEN salesman) the he would appear on the bottom of resulting table? – Pan Tomima May 23 '22 at 14:55
  • yes, you're right. however, not necessarily always at the very bottom – samkart May 23 '22 at 15:01
  • 1
    That's an Oracle query, so why the `postgresql` tag. –  May 23 '22 at 15:07
  • 1
    Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 –  May 23 '22 at 15:08
  • Thanks @samkart, you've helped me a lot. When you say "not necessarily always at the very bottom " you mean that it can behave both like a BFS and DFS depending on our intention? Can we do then any tree traversal order, like A*? – Pan Tomima May 23 '22 at 15:27
  • not that I know of. the doc mentioned top-down only. – samkart May 23 '22 at 15:29
  • @a_horse_with_no_name I think that image is just a screengrab from the [article linked above](https://www.highgo.ca/2021/04/09/how-to-run-hierarchical-queries-with-postgresql/) – samkart May 24 '22 at 07:24

0 Answers0