Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Adjacency model problem

Author  Topic  ZoeNet Starting Member 1 Post Posted - 2007-10-22 : 09:03:55 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 examples. The category_cat table containts drill-downs of pets (from animal type to animal color). I have the table product_pdt that containts "I love 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 love shirts" for dogs. Desired result I love dogs I love chihuahuas I love brown chihuahuas I love black chihuahuas I love poodles I did a research for old post and hope it's not as complicated as the ones I found. 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 love dogs') INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt]) VALUES (2, 'I love chihuahuas') INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt]) VALUES (3, 'I love brown chihuahuas') INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt]) VALUES (4, 'I love black chihuahuas') INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt]) VALUES (5, 'I love poodles') INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt]) VALUES (6, 'I love cats') INSERT INTO [product_pdt (yannick_admin)] ([id_pdt], [name_pdt]) VALUES (7, 'I love 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)