SQL Server 2012 – Query for Hierarchical Data

querysql server

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

Best Answer

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

Related Question