Adjacency model problem

[ZoeNet]

Hi all,

The table structures are below.

The table category_cat is built on the adjacency model (where the parent and child entries are both stored in the same table). For my problem, I've created 3 tables for example. The category_cat table containts drill-downs of pets (from animal type to animal color). I have the table product_pdt that containts "I hate shirts" of every animal category. And the table pdttocat_ptc joins the other 2 tables. So my problem is that I need to fetch all the records for one top category. For example if I want all the "I hate shirst" for dogs.

Desired result

I hate dogs
I hate chihuahuas
I hate brown chihuahuas
I hate black chihuahuas
I hate poodles

Thanks for your help

CREATE TABLE [category_cat] (
[id_cat] int IDENTITY NOT NULL,
[idcat_cat] int NOT NULL,
[name_cat] varchar(50) NOT NULL,
PRIMARY KEY CLUSTERED ([id_cat])
)

CREATE TABLE [product_pdt] (
[id_pdt] int IDENTITY NOT NULL,
[name_pdt] varchar(50) NOT NULL,
PRIMARY KEY CLUSTERED ([id_pdt])
)

CREATE TABLE [pdttocat_ptc] (
[id_ptc] int IDENTITY NOT NULL,
[idpdt_ptc] int NOT NULL,
[idcat_ptc] int NOT NULL,
PRIMARY KEY CLUSTERED ([id_ptc])
)


INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (1, 0, 'Dogs')

INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (2, 1, 'Chihuahua')

INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (3, 2, 'Brown')

INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (4, 2, 'Black')

INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (5, 1, 'Poodle')

INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (6, 0, 'Cats')

INSERT INTO [category_cat] ([id_cat], [idcat_cat], [name_cat])
VALUES (7, 6, 'Siamese')


INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (1, 'I hate dogs')

INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (2, 'I hate chihuahuas')

INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (3, 'I hate brown chihuahuas')

INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (4, 'I hate black chihuahuas')

INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (5, 'I hate poodles')

INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (6, 'I hate cats')

INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt])
VALUES (7, 'I hate siamese')


INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (1, 1, 1)

INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (2, 2, 2)

INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (3, 3, 3)

INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (4, 4, 4)

INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (5, 5, 5)

INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (6, 6, 6)

INSERT INTO [pdttocat_ptc (yannick_admin)] ([id_ptc], [idpdt_ptc], [idcat_ptc])
VALUES (7, 7, 7)