SQL query: linking external links

Question

I have two tables, tabSparePartand tabSparePartCategory. Each spare part is classified as a spare part. I need all the spare parts that belong to a certain category. But the problem is that the spare part category may be a “subcategory” of another, they refer to each other (the “main categories” have a “zero” in this FK column).

Say I need all spare parts with fiSparePartCategory=1and all spare parts that belong to a category that is a “subcategory” category=1.

How to write an SQL query that returns all the spare parts, regardless of how many levels of subcategories there are. 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

Image link: http://www.bilder-hochladen.net/files/4709-lg-jpg.html

EDIT . The following is another static approach that 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 generic table expression for 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