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
Best Answer
I had to clean up your DDL/DML a little as it had a syntax error:
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:
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 witha.productId AS baseID