Write a query to get hierarchical data in SQL Server 2012

Question

I have three tables: product, users, and orders

product: list of all products and their statuses. This table has a field called previousProductId. A product's code can be changed multiple times and if it is then the previousProductId will contain it's immediate predecessors code. For example, if a product has code 1057 and then a new line is added for this product with a new code 1060 then the previousLineId will have a code of 1057. This is repeated every time a new line with a new code is added for an existing product.
users: list of all users
orders: list of all orders placed by users

Now I have to write a query to get all orders with active products. I have to make sure all the users who ordered the previous versions of an active product are also included in the result set. I want to use recursive CTE to go up the chain to retrieve all previous product Ids for a given product and retrieve the necessary information that way, but that method seems a little too bulky. I wanted to know if there is a better and simpler way of doing it. Thank you!

Create table #product
 (
 productId Int primary key Identity(1,1),
 name varchar(100),
 price numeric(18,5),
 previousProductId Int,
 active bit
 )
 
 Create table #users
 (
 userId Int Primary key,
 name varchar(100)
 )
 
 Create table #orders
 (
 orderId Int primary key,
 userId Int,
 productId Int
 )
 
 Insert Into #product values ('Sony TV', 200.23, null, 0), ('Sony Tv New', 240.43, 1, 0), ('Apple Watch', 300.45, null, 1)
 ,('Samsung Mobile',1050, null,0),('Sony TV Advanced', 400, 2,1)
 
 Insert Into #users values (1,'John'), (2,'Mary'), (3,'Kevin'), (4,'Joe'), (5,'Andy'),(6,'Jim'),(7,'Pam')
 
 Insert Into #orders values (1, 1, 1), (2, 2, 1),(3, 3,2), (4,4,3), (5,5,3),(6,6,4),(7,7,5)
 
 select t_p.productId, t_p.name, t_p.price, t_p.previousProductId, t_p.active
,t_u.name as username, t_o.orderId
 from #product t_p
 join #orders t_o
 on t_p.productId = t_o.productId 
 join #users t_u
 on t_u.userID = t_o.userId
 where t_p.active = 1
 
 drop table if exists #product
 drop table if exists #orders
 drop table if exists #users

Expected Result: enter image description here

Answer 1

I had to clean up your DDL/DML a little as it had a syntax error:

DECLARE @product TABLE (productId INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(100), price NUMERIC(18,5), previousProductId INT, active BIT)
DECLARE @users TABLE (userId INT PRIMARY KEY, name VARCHAR(100))
DECLARE @orders TABLE (orderId INT PRIMARY KEY, userId INT, productId INT)
 
INSERT INTO @product (name, price, previousProductId, active) VALUES 
('Sony TV', 200.23, null, 0), ('Sony Tv New', 240.43, 1, 0), ('Apple Watch', 300.45, null, 1),
('Samsung Mobile',1050, null,0),('Sony TV Advanced', 400, 2,1)
INSERT INTO @users (userId, name) VALUES 
(1,'John'), (2,'Mary'), (3,'Kevin'), (4,'Joe'), (5,'Andy'), (6,'Jim'), (7,'Pam')
INSERT INTO @orders (orderId, userId, productID) VALUES 
(1, 1, 1), (2, 2, 1), (3, 3,2), (4,4,3), 
(5,5,3), (6,6,4), (7,7,5)

I prefer table variables for this, as there's no clean up to worry about.

On to your question. I'm not sure your defined expected output is what you really want, as it makes no reference to a base or parent id. It also seems to be missing orderId 6.

A rCTE technique for getting the 'base' (ultimate parent) could be written like this:

;WITH baseProduct AS (
SELECT productId AS baseID, productId
 FROM @product
 WHERE previousProductId IS NULL
 UNION ALL
SELECT a.baseID AS baseID, r.productId
 FROM @product r
 INNER JOIN baseProduct a
 ON r.previousProductId = a.productId
)
SELECT p.productId, p.name, p.price, p.previousProductId, p.active, u.name, o.orderId, bp.baseID
 FROM @orders o
 LEFT OUTER JOIN baseProduct bp
 ON o.productId = bp.productId
 INNER JOIN @product p
 ON o.productId = p.productId
 INNER JOIN @users u
 ON o.userId = u.userId

productId	name	price	previousProductId	active	name	orderId	baseID
1	Sony TV	200.23000	0	John	1	1
1	Sony TV	200.23000	0	Mary	2	1
2	Sony Tv New	240.43000	1	0	Kevin	3	1
3	Apple Watch	300.45000	1	Joe	4	3
3	Apple Watch	300.45000	1	Andy	5	3
4	Samsung Mobile	1050.00000	0	Jim	6	4
5	Sony TV Advanced	400.00000	2	1	Pam	7	1

I may have misunderstood your intent, and you actually just want the previousProductID (in the case of orderId 7, 2 instead of 1) if that is the case you should be able to replace the a.baseID AS baseID in the recursive side of the rCTE with a.productId AS baseID

• lifeisajourney: Thank you for the answer. I'm aware of the recursive cte method and mentioned that I found it too bulky. I probably should have posted my recursive cte solution in the question. Sorry about the confusion. I wanted to know if there's any other simpler way to use this without using the recursive cte. What I want is: if a user with an active product is picked and if this active product has previous versions then all the users who are associated with those previous versions should be picked as well.
• J.D.: @lifeisajourney Not sure why you'd say 10 lines of code for recursion is bulky, but that's typically one of the better ways to solve this kind of problem. It's generally one of the most performant ways to solve it too.
• Martin Smith: @lifeisajourney if this involves traversing a chain of previous versions of arbitrary length then recursive CTE is probably the least "bulky" method. You can just hardcode some outer self joins to join product to previous version but then you don't travel any more steps than number of joins. Or you can replace the recursive CTE with a while loop and that is likely to be more code not less
• lifeisajourney: Thank you all. I was trying to overoptimize the code by finding a way to accomplish this with a simple join somehow. A good lesson learned.
• J.D.: @lifeisajourney Gotcha, yea it's not possible with a single join. Using the right amount of joins will be dependent on how many levels there are in the hierarchy, which typically most people don't have a constant number of levels in their hierarchical data. Recursive CTEs FTW!