|
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)
|
