February 14, 2013 at 2:05 am
Hi,
in my database i have these two table
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
the category table is a self joined table with the CAT_ID_PARENT field
i have used this recursive query to display all childrens for a given category
WITH tree (data, id)
AS (SELECT CAT_DESIGNATION, ID_CATEGORIE
FROM CATEGORIES s
WHERE ID_CATEGORIE = 8
UNION ALL
SELECT CAT_DESIGNATION, ID_CATEGORIE
FROM CATEGORIES V
INNER JOIN tree t
ON t.id = V.CAT_ID_PARENT)
SELECT data , id
FROM tree
now i want all the products from the produits table that their PRD_ID_CATEGORIE IN the previous query
btw : is it possible to add a parameter to the first query , i got an error when trying :
WITH tree (data, id)
AS (SELECT CAT_DESIGNATION, ID_CATEGORIE
FROM CATEGORIES s
WHERE ID_CATEGORIE = @C
UNION ALL
SELECT CAT_DESIGNATION, ID_CATEGORIE
FROM CATEGORIES V
INNER JOIN tree t
ON t.id = V.CAT_ID_PARENT)
SELECT data , id
FROM tree
thanks and good day
February 14, 2013 at 2:16 am
Just need to do an inner join from the tree CTE to the Produits table
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply