writing SQL query with nested sets

Question

I need to write a query to perform a search where all results are matched by a category.
The categories have subcategories and they work as a nested set model, and each has a lft and a rgt column.

If cat2, cat3 and cat4 are childs of cat1 then their .lft values are between cat1.lft and cat1.rgt

all categories are stored in a CATEGORIES table and have an ID as primary key.

what i need is given a category ID find all items on ITEMS table that belong to that category but also all items that belong to category sub-categories.

the query im looking for would be something like this

 SELECT i.id, i.name
 FROM items AS i, categories AS c
 WHERE i.category_lft BETWEEN c.given_cat_id_lft AND c.given_cat_id_rgt

what i mean is i get categories primary key (ID) but on the query i need to work with their rgt and ltf values

Should I use subqueries or maybe joins? And how should the query be? I really don't have that experience in SQL.

See this [question](http://stackoverflow.com/questions/316267/help-with-writing-a-sql-query-for-nested-sets). — Andrew Logvinov, Feb 27 '12 at 07:56
Does the items table store the item's category_id (as assumed in ypercube's answer), or its category_lft value (as implied in the example query supplied in the question)? — , Feb 27 '12 at 12:02

score 1 · Answer 1 · answered Feb 27 '12 at 10:56

1

 SELECT i.id
      , i.name
 FROM items AS i
   JOIN categories AS c
     ON i.category_id = c.id
   JOIN categories AS myc
     ON c.category_lft BETWEEN myc.lft AND myc.rgt
 WHERE myc.id = @GivenCategoryID

answered Feb 27 '12 at 10:56

ypercubeᵀᴹ

113,259
19
174
235

score 0 · Answer 2 · answered Feb 27 '12 at 10:47

Try this:

CREATE table #Category(
id      INT NOT NULL,
name    VARCHAR(20) NOT NULL,
parentid    INT NOT NULL
)

INSERT INTO #Category (id, name, parentid) VALUES
(1, 'parts', 1),
(2, 'processor', 1),
(3, 'AMD', 2),
(4, 'Intel', 2),
(5, 'Hard Disk', 1),
(6, 'Memory', 1),
(7, 'DDR1', 6),
(8, 'DDR2', 6),
(9, 'DDR3', 6),
(10, '533Mhz', 7),
(11, '667Mhz', 8),
(12, '800Mhz', 8)

CREATE table #Items(
id      INT NOT NULL,
name    VARCHAR(20) NOT NULL,
categoryId  INT NOT NULL
)

INSERT INTO #Items (id, name, categoryId) VALUES
(1, 'AMD Phenom', 3),
(2, 'AMD Sempron', 3),
(3, 'AMD Athlon', 3),
(4, 'Intel core 2 duo', 4),
(5, 'Intel core i3', 4),
(6, 'DDR2 1GB 6667Mhz', 11),
(7, 'Intel 8080', 4),
(8, 'Intel 80286', 4),
(9, 'Intel 80386', 4),
(10, 'Intel 80486', 4),
(11, 'Intel Pentuim', 4),
(12, 'Intel Pentium I', 4)


DECLARE @TopLevelId INT; -- top level category Id
SET @TopLevelId = 2;   -- top level category = AMD processors

WITH CTE AS
(SELECT id, name, parentid, id as Adam, 1 as level
FROM #Category
WHERE id = @TopLevelId
UNION ALL
SELECT #Category.id, #Category.name, #Category.parentid, CTE.Adam, CTE.level + 1
FROM #Category 
INNER JOIN CTE ON
    #Category.parentid = CTE.id
WHERE #Category.id <> @TopLevelId
)
SELECT CTE.id CategoryId, CTE.name as Category, #Items.name as Item
FROM CTE
INNER JOIN #Items ON
    CTE.id = #Items.categoryId
ORDER BY level, CTE.id


DROP table #Category
DROP table #Items

The question relates to MySQL, not SQLServer. – Feb 27 '12 at 15:57 — , Feb 27 '12 at 15:57

Wim · Answer 3 · 2012-02-27T11:09:15.540

You will need recursive SQL to solve this.

First find all subcategories that have the given category as parent, grandparent, grandgrandparent, ... (don't forget to include the given category itself).

Then JOIN these subcategories with the categories in your Items table.

I would forget about the lft and rght values. It will be a hell of a job to make that work. (how would the BETWEEN work? based on the values of the Id's? Can you imagine the consequences of inserting a subcategory somewhere in between existing subcategories?)

writing SQL query with nested sets

3 Answers3