how i can get all products from parentid in SQL Server?

I have a category table to name TBL_category And includes the following fields:

nid i identity field

nid    parentid    name
 1        0        mobile
 2        0        tablet
 3        1        apple
 4        3        iphone
 5        2        apple
 6        5        ipad

and i have a production Table TBL_Productions and foreign key is to nid And includes the following fields:

productid is identity

 productid   nid        name
 1            4        iphone x
 2            4        iphone xs
 3            4        iphone 11
 4            4        iphone 11 pro
 5            6        ipad air 2
 6            6        ipad mini
 7            6        ipad new
 8            6        ipad pro

My question is:

How can I display all products, when user selected mobile category ?

I have nothing to do with the subcategories, I want all products to be displayed when the mobile or tablet category is selected.

thanks

Answer

One option uses a recursive query to recover the “mobile” category and all of its descendants, and then brings the products table with a join:

with cte as (
    select nid from tbl_category where name = 'mobile'
    union all
    select ca.nid from cte ct inner join tbl_category ca on ca.parentid = ct.nid
)
select p.*
from tbl_productions p
inner join cte ct on ct.nid = p.nid