UptoSkills Logo
Login
Find All Ancestors of a ProductHard

Find All Ancestors of a Product

HardSQLPractice library · +30 XPrecursive ctesqlhierarchy

A manufacturing company organizes its products into categories and subcategories. Each subcategory can have a parent category, forming a hierarchical structure. You need to find all the ancestors (parent, grandparent, great-grandparent, etc.) for a specific product category.

Tables:

  • categories: Stores category information.
    • category_id (integer, PK): Unique ID for the category.
    • name (varchar(100), NOT NULL): Name of the category.
    • parent_id (integer, FK to categories.category_id): ID of the parent category, NULL for top-level categories.

Return the category_id and name of all ancestor categories for the category with category_id = 101. The results should be ordered by the level of ancestry (direct parent first, then grandparent, and so on). Ties in level are broken by category_id.

Handle NULLs: If a category has no parent, it is not an ancestor of itself. Empty results: If the category has no ancestors, return an empty set.

Tables

CREATE TABLE categories (
    category_id integer PRIMARY KEY,
    name varchar(100) NOT NULL,
    parent_id integer REFERENCES categories(category_id)
);

Example data

INSERT INTO categories (category_id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Gaming Laptops', 3),
(10, 'Mobile Phones', 1),
(11, 'Smartphones', 10),
(100, 'Clothing', NULL),
(101, 'Menswear', 100),
(102, 'Shirts', 101),
(103, 'T-Shirts', 102),
(104, 'Formal Shirts', 102),
(200, 'Home Goods', NULL),
(201, 'Kitchenware', 200),
(202, 'Utensils', 201);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems