Oracle SQL can do hierarchical queries since v2, using their proprietary CONNECT BY syntax. In their latest 11g release 2, they added recursive subquery factoring, also known as the recursive with clause. This is the ANSI standard, and if I understand correctly, this one has been implemented by other RDBMS vendors as well. When comparin...
I have a database table, with people identified by a name, a job and a city. I have a second table that contains a hierarchical representation of every job in the company in every city. Suppose I have 3 people in the people table: [name(PK),title,city] Jim, Salesman, Houston Jane, Associate Marketer, Chicago Bill, Cashier, New York ...
Here's the documentation: http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm Here's the execution order it describes: A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates. The CONNECT BY condition is evaluated. Any remaining WHERE claus...
I have the following SQL statement: SELECT CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" , ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID, LEVEL -1 "LEVEL" FROM ANIMALS START WITH ANIMAL_ID IN( '2360000002558' ) CONNECT BY ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < ...