CREATE TABLE [dbo].[CATEGORIES](
[ID_CATEGORIE] [int] IDENTITY(1,1) NOT NULL primary key,
[CAT_ID_PARENT] [int] NULL,
[CAT_DESIGNATION] [varchar](150) NOT NULL,
[CAT_LEVEL] [int] )

CREATE TABLE [dbo].[PRODUITS](
[ID_PRODUIT] [int] IDENTITY(1,1) NOT NULL primary key,
[PRD_ID_CATEGORIE] [int] NOT NULL, /* foreign key */
[PRD_DESIGNATION] [varchar](150) NOT NULL,
[PRD_IMAGE] [varchar](150) NULL)

INSERT INTO CATEGORIES VALUES 
(null,'parent',1),--1
(1,'child1',2),--2
(1,'child2',2),--3
(2,'grandchild1',3),--4
(2,'grandchild2',3),--5
(3,'grandchild3',3),--6
(3,'grandchild4',3)--7

INSERT INTO PRODUITS VALUES
(1,'parent','parentimage'),
(2,'child1','child1image'),
(3,'child2','child2image'),
(4,'grandchild1','grandchild1image'),
(5,'grandchild2','grandchild2image'),
(6,'grandchild3','grandchild3image'),
(7,'grandchild4','grandchild4image')

DECLARE @ID INT = 1

;WITH tree (data, id)
AS (SELECT CAT_DESIGNATION, ID_CATEGORIE
FROM CATEGORIES s
WHERE ID_CATEGORIE = @ID
UNION ALL
SELECT CAT_DESIGNATION, ID_CATEGORIE
FROM CATEGORIES V
INNER JOIN tree t
ON t.id = V.CAT_ID_PARENT)
SELECT p.*
FROM PRODUITS p
inner join tree t on
p.PRD_ID_CATEGORIE = t.id