Self-relational table and recursive SQL function

Question

I have a category table with:

• CategoryId
• parentCategoryID
• CategoryName

and a table of elements with:

• Itemid
• CategoryId
• ITEMNAME

I am using MySQL. I want to write a query that will return the number of elements in a category, taking into account the category identifier. The request should return a total score for all elements in all subcategories of this category.

Hope this makes sense. Sorry if I do not use the correct nomenclature.

Answer 1

? " ", , , . .

, :

http://pugs.postgresql.org/files/ModelingTrees.pdf

Answer 2

, , . : http://intelligent-enterprise.informationweek.com/001020/celko1_1.jhtml;jsessionid=AFUXE0ZF4PTNXQE1GHPSKH4ATMY32JVN

, : : http://explainextended.com/2010/04/18/hierarchical-query-in-mysql-limiting-parents/

Answer 3

, , , join/alias . , -.

, "", - :

SELECT top.categoryID, top.categoryName, bottom.categoryID, bottom.categoryName,
    COUNT (items.itemID)
FROM categories AS top
LEFT JOIN categories AS bottom ON top.categoryID = bottom.parentCategoryID
LEFT JOIN items ON bottom.categoryID = items.categoryID
WHERE (bottom.categoryID = $your_category)
GROUP BY top.categoryID, bottom.categoryID

, WHERE, .

Answer 4

, , Jeff Dege, Adjacency:

, , ( SQL BETWEEN , , ), ( ) LIKE '[path]%' ( , MySQL, ) .

, . (, 1.2.3.).

You might want to conduct your own tests to compare these approaches, especially if you have many categories (several thousand or more).