You can join the courses at devgym.oracle.com/pls/apex/dg/class/databases-for-developers-foundations.html devgym.oracle.com/pls/apex/dg/class/databases-for-developers-next-level.html
@@TheMagicofSQL Yeah it was a bit off the screen..but I see it now.... My only question is can you look up the tree as well as down so if I select a middle of hte road tree item can I still see the whole tree or do I always need to start at the root..
You can go up or down the tree, just flip the tables in the join criteria. If you're asking if you can go up AND down in one query... yes it's possible. You need to do both the joins in both directions and have cycle detection. Here's a connect by example: with rws as ( select level x, lag ( level ) over ( order by level ) y from dual connect by level
@@TheMagicofSQL it's a way to manage hierarchical data where a separate table is used to store the boundaries of the node in the tree. You perform the query on this separate table instead of the actual table. See en.wikipedia.org/wiki/Nested_set_model
Gotcha. It really depends on the query you're running. If you need to traverse the tree, I suspect connect by/recursive with will be faster. It really comes down to the data and your query though
You can use a subquery to identify the root row(s). This works for connect by (start with col in ( select ... ) ) and the base query in recursive with.
Connect by is specific to Oracle Database; I imagine recursive with works on MS, but I don't know the details You have to listen to music whichever database you use ;)
Oh.. to take care of divorces, re marriages, children from multiple marriages.. and… shocker.. marriages of cousins (at a broad 4th/5th level).. i had to patch up the data model.. !!
![ประสบการณ์ จ้าง Developer ที่ผมจะไม่มีวันลืม [ภาค2]](http://i.ytimg.com/vi/pZbsSDRrmXA/mqdefault.jpg)
Fantastic video!! it's 8pm here at my office and I'm dancing because this function has helped me a lot.
Awesome :) Got a video of you dancing? ;)
@@TheMagicofSQLunfortunately no dance video from him😢
No where on web I found such helpful fundamental description on hierarchy..!! Thank you 😊
Thanks! Glad this helped you :)
So amazing ! Keep producing videos, please. Thank you !
Snowden is now teaching SQL seems FUN
i love the effort
just made reading a while wall of text easier but is there any tutorial or exercise i can practise this with?
You can find an interactive tutorial at livesql.oracle.com/apex/livesql/file/tutorial_GQMLEEPG5ARVSIFGQRD3SES92.html
thank you for this video
Great Video!, Helped me understand the concept .
You're welcome, glad you found this useful
Where can I find the sql queries shown in this video?
You can join the courses at
devgym.oracle.com/pls/apex/dg/class/databases-for-developers-foundations.html
devgym.oracle.com/pls/apex/dg/class/databases-for-developers-next-level.html
Ok...why in the recursive with do you have a "yf." there is no alias refereing to a yf table...
You mean here? th-cam.com/video/pB_nOIk3mmY/w-d-xo.html
It's on the join to your_family yf in the second block of the union all
@@TheMagicofSQL Yeah it was a bit off the screen..but I see it now....
My only question is can you look up the tree as well as down so if I select a middle of hte road tree item can I still see the whole tree or do I always need to start at the root..
You can go up or down the tree, just flip the tables in the join criteria.
If you're asking if you can go up AND down in one query... yes it's possible. You need to do both the joins in both directions and have cycle detection. Here's a connect by example:
with rws as (
select level x, lag ( level ) over ( order by level ) y
from dual
connect by level
Is the hierachical query faster than the nested set model?
I'm not familiar with the nested set model - could you clarify what this is?
@@TheMagicofSQL it's a way to manage hierarchical data where a separate table is used to store the boundaries of the node in the tree. You perform the query on this separate table instead of the actual table. See en.wikipedia.org/wiki/Nested_set_model
Gotcha. It really depends on the query you're running. If you need to traverse the tree, I suspect connect by/recursive with will be faster. It really comes down to the data and your query though
hi just inquiry can i use those functions in mysql and php?
MySQL does support recursive with/CTEs. I'm not sure if/how it covers all the features discussed here though.
WHAT CAN I DO WITH MYSQL WHEN I WANT TO RESOLVE THE PROBLEM LIKE THAT?
I focus on Oracle Database here, so I'm not sure; sorry!
What about Binary.
What to do if you dont know the root? Can this be done in the same statement or should this be done in a seperate querry?
You can use a subquery to identify the root row(s). This works for connect by (start with col in ( select ... ) ) and the base query in recursive with.
Thanks much, So this is for Oracle, I think something similar can be done on MS? (and without music-) Thanks much
Connect by is specific to Oracle Database; I imagine recursive with works on MS, but I don't know the details
You have to listen to music whichever database you use ;)
Not able to understand the syntax.
What exactly are you struggling with?
Oh.. to take care of divorces, re marriages, children from multiple marriages.. and… shocker.. marriages of cousins (at a broad 4th/5th level).. i had to patch up the data model.. !!
Yeah, family trees can be tricky!
The background music needs to be changed.
"But, of course, in real life everyone has two parents" In 2022 is not so obvious...
Video s good but this damn stupid BG music is really distracting
Thumbed down, misleading title. This is "Oracle Databases" for developers
too much illustration is distracting and annoying.
What specifically is distracting you?