Managing SQL hierarchy (Recursive)

1

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
----+-----------------+------------------
110 | WALMART | NULL
111 | WALMART TEXAS | 110
112 | WALMART DALLAS | 111
113 | WALMART HOUSTON | 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

2

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
	(1002,'User1',113),
	(1003,'User2',114),
	(1004,'User3',111);

create table #companies
(id int, company_name varchar(64), parent_company_id int);
insert into #companies values
	(110,'WALMART',NULL),
	(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
(
	select
		u.user_id,
		c.id as company_id,
		c.parent_company_id
	from
		#users u
		inner join #companies c on
			c.id = u.company_id

	union all

	select 
		r.user_id,
		r.parent_company_id as company_id,
		c.parent_company_id
	from
		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.