7 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

Recently I asked a question in dba.stackexchange the questionquestion was about data hierarchy in sql and a guy named chris allenchris allen answered the question and I really loved it but when I try to understand that query much more I got confused.

Here's the query:

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused.

Here's the query:

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused.

Here's the query:

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

6 added 4 characters in body
source | link

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused  .Here's

Here's the qureyquery:

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused  .Here's the qurey

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused.

Here's the query:

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

5 spelling mistakes
source | link

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused .Here's the qurey

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. fistFirst he named four columns as each level 1,2,3 etc
  2. eachEach time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. whereWhere condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am puttingputting a new question

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused .Here's the qurey

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. fist he named four columns as each level 1,2,3 etc
  2. each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that query much more I got confused .Here's the qurey

Res_id  Res_name         Man_id
1           sam             3
2           Biju            4
3           adrian          Null
4           Helen           3
5           Micah           4


select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3,
 level4.res_name as level4,
 level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0

I did several approaches to crack and was in vain. I think I am missing something well my findings are given below

  1. First he named four columns as each level 1,2,3 etc
  2. Each time he left joined the same table 4 time each with different aliases
  3. By giving condition on each join the table is getting changed to next level
  4. Where condition to specify the manager

The part I failed to understand is the join he made even though I know the use of left join where we will get the columns of left table and on the right if no match is found it will be NULL.So I am looking for some visualization of the join happening here or some understanding of the joins in depth of this particular scenario.If the question doesn't fit here I will delete it . I also asked the same in the question I posted but there was no reply that is why I am putting a new question

4 added an sample data
source | link
    Tweeted twitter.com/#!/StackDBAs/status/297693109335257088
3 added 531 characters in body
source | link
2 added 30 characters in body
source | link
1
source | link