SQLTeam.com | Weblogs | Forums

Managing SQL hierarchy (Recursive)



I have a below "User_Info". It carries all user info and the ID of the company that each user belongs to.

User_Id | User_Name | Company_Id
1002 | User1 | 113
1003 | User2 | 114
1004 | User3 | 111
I have another table "Company" which has Company Information and its associated Parent Company.

id | Company_Name | Parent_Company_Id
111 | WALMART TEXAS | 110
112 | WALMART DALLAS | 111
114 | WALMART KATY | 113
How do I get the below output? The Company_id in the User_Info table should list all the hierarchical companies under that Company_id.

user_id | company_id
1002 | 113
1002 | 111
1002 | 110
1003 | 114
1003 | 111
1003 | 110
1004 | 111
1004 | 110


When you post a question, it would be very helpful if you post sample data and tables that someone can copy and paste to their SSMS window and write a query against. In the absence of that, the person who wants to respond has to spend time creating such sample data, which often times is more time consuming than writing the actual query. If you do that, you will get faster and more accurate responses.

In your case, post something like what I am showing below:

Create table #users(user_id int, user_name varchar(32), company_id int);
insert into #users values

create table #companies
(id int, company_name varchar(64), parent_company_id int);
insert into #companies values
	(111,'WALMART TEXAS',110),
	(112,'WALMART DALLAS',111),
	(113,'WALMART HOUSTON',111),
	(114,'WALMART KATY',113);

I realize that you are new to the forum, so this is all for future reference.

In any case, once you provide the sample data, it is easy to write a query. So for your problem, the query can be like shown below:

;with rec as
		c.id as company_id,
		#users u
		inner join #companies c on
			c.id = u.company_id

	union all

		r.parent_company_id as company_id,
		rec r
		inner join #companies c on
			c.id = r.parent_company_id
select user_id, company_id from rec
order by user_id, company_id desc 

This is a recursive common table expression query. Some details here.