SQL query: self-referencing foreign key relationship

Question

I have two tables, tabSparePart and tabSparePartCategory. Every spare part belongs to a spare part category. I need all spare parts that belong to a specific category. But the problem is that a spare part category could be a "subcategory" of another, they reference each other (the "main categories" have 'null' in this FK column).

Let's say I need all spare parts with fiSparePartCategory=1 and all spare parts that belong to a category that is a "subcategory" of category=1.

How to write the SQL query that returns all spare parts regardless of how many levels of subcategories there are. I hope you understand my requirement.

The following is an illustration of what I have. How to make it dynamic so that it works regardless of the number of subcategories?

Thanks, Tim

Link to image: http://www.bilder-hochladen.net/files/4709-lg-jpg.html

EDIT: Following is an other static approach which works when there is only one level of subcategory:

SELECT     SparePartName
FROM         tabSparePart
WHERE     (fiSparePartCategory IN
               (SELECT     idSparePartCategory
                     FROM          tabSparePartCategory
                     WHERE      (idSparePartCategory = 1) OR
                                (fiSparePartCategory = 1)))

Answer 1

You can use a recursive Common Table Expression for this.

In your case, you would need to get all sparepart category ids for a specific main category id and join that with the spareparts. Something like this:

WITH SparePartCategories(CategoryId) AS
(
    SELECT c.idSparePartCategory
    FROM tabSparePartCategory c
    WHERE c.idSparePartCategory = 1

    UNION ALL

    SELECT c.idSparePartCategory
    FROM tabSparePartCategory c
    JOIN SparePartCategories parent ON c.fiSparePartCategory = parent.CategoryId
)
SELECT sp.SparePartName
FROM tabSparePart sp
JOIN SparePartCategories spc ON sp.fiSparePartCategory = spc.CategoryId