How join two tables? Hierarchical table

• mesteruh

  Valued Member

  Points: 71

  More actions

  November 30, 2013 at 1:51 am

  #283042

  I have two table. Department is hierarchical table.

  Department

  --- id (int primary key)

  --- name (varchar)

  --- parent (int)

  Users

  --- Id

  --- name

  --- department_id

  This query return all data from departments. But i cannot understand how get data from users

  SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3

  FROM Department AS t1

  LEFT JOIN Department AS t2 ON t2.parent = t1.id

  LEFT JOIN Department AS t3 ON t3.parent = t2.id

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  November 30, 2013 at 2:43 pm

  #1669921

  mesteruh (11/30/2013)

  ---

  I have two table. Department is hierarchical table.

  Department

  --- id (int primary key)

  --- name (varchar)

  --- parent (int)

  Users

  --- Id

  --- name

  --- department_id

  This query return all data from departments. But i cannot understand how get data from users

  SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3

  FROM Department AS t1

  LEFT JOIN Department AS t2 ON t2.parent = t1.id

  LEFT JOIN Department AS t3 ON t3.parent = t2.id

  90% of the solution is the correct identification of the problem. What do you want the output to look like and how do you want it grouped?

  My recommendation is to provide some data in a readily consumable format. Please don't assume how to do that. Please see the first link in my signature line below under "Helpful Links". Once you have that data, then provide an example of how you want that data to look.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)