Adjacency List Model

myAdjacencyListModel .sql

CREATE TABLE [dbo].[cats](
	[ID] [int] IDENTITY(0,1) PRIMARY KEY,
	[SuperCatID] [int] NULL,
	[CatName] [nvarchar](50) ,
	[CatLevel] [tinyint]   -- this field inserted with a trigger not directly
	)
  
-- have a relation between ID and SuperCatID
ALTER TABLE [dbo].[cats]  WITH CHECK ADD  CONSTRAINT [FK_cats_cats] FOREIGN KEY([SuperCatID])
REFERENCES [dbo].[cats] ([ID])
GO

ALTER TABLE [dbo].[cats] CHECK CONSTRAINT [FK_cats_cats]
GO

/*
    how to get this result:
    
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+


*/