修改SQL查询以支持多级父分类及单分类的商品查询

Got it, let's fix this SQL query so it works seamlessly for both top-level parent categories (like cat_id=21 "Properties") and individual child categories (like cat_id=35 "Houses and villas").

The Problem with Your Current Query #

Your existing query only looks one level deep in the category hierarchy. When you target cat_id=21, it only pulls categories directly under it (cat_id=32 "For Rent")—but misses the grandchild categories (34, 35, 36) that are nested under 32. That's why it fails to return all products under the top-level category.

The Solution: Recursive CTE to Fetch All Descendant Categories #

We need to use a recursive Common Table Expression (CTE) to traverse the entire category hierarchy, grabbing every child, grandchild, and so on, of your target category. Here's the adjusted query:

WITH RECURSIVE category_hierarchy AS (
    -- Base case: Start with your target category (replace with any cat_id you need)
    SELECT cat_id, cat_name, parent_id
    FROM categories
    WHERE cat_id = 21 -- Swap this with 35 to get products for just Houses and villas
    UNION ALL
    -- Recursive case: Keep fetching all child categories of the ones we've already found
    SELECT c.cat_id, c.cat_name, c.parent_id
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_id = ch.cat_id
)
-- Join the full category hierarchy with products to get the relevant items
SELECT 
    p.ProductID, 
    p.ProductName, -- Note: Your original query used ProductTitle, but your products table has ProductName—adjust if needed
    -- p.SalePrice, p.C_Date, p.ProductShortDescription, -- Uncomment these if they exist in your actual products table
    ch.cat_name, 
    ch.cat_id 
FROM products p 
JOIN category_hierarchy ch ON p.CategoryID = ch.cat_id;

How This Works #

• Recursive CTE Breakdown: The CTE starts with your target category (whether it's top-level or a deep child). Then it repeatedly joins back to the categories table to pull in all child categories of the ones we've already collected, until there are no more children left.
• Flexible Targeting: Just change the cat_id = 21 in the base case to any category ID you want to query:
  • Use 21 to get all products under "Properties" (including all nested subcategories)
  • Use 35 to get only products under "Houses and villas"
• Schema Alignment: I noticed a mismatch between your query (ProductTitle) and the products table you provided (ProductName). Make sure to adjust column names to match your actual database schema.

Testing the Query #

• When targeting cat_id=21, the CTE will include categories 21, 32, 34, 35, 36—so all products linked to these IDs (villa, Flat, appartment, Builder) will be returned.
• When targeting cat_id=35, the CTE will only include category 35—so you'll get just the "villa" and "Flat" products.

This approach handles any depth of category nesting, making it a robust solution for your needs.

内容的提问来源于stack exchange，提问作者Ajeesh TP