Subcategories with Parent Categories

The problem

Brightlane's product team is reviewing the category hierarchy and needs each subcategory paired with its parent category.

Write a query to return the subcategory name and parent category name for every subcategory that belongs to another category.

Assumptions:

The categories table contains every defined category in the catalogue.
The parent_id column links each subcategory to its parent — also a row in categories.
Top-level categories have parent_id set to NULL and will not appear in the result.

Output:

One row per subcategory, with columns subcategory_name and parent_category_name.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  c.name AS subcategory_name,
  p.name AS parent_category_name
FROM
  categories c
  JOIN categories p ON c.parent_id = p.id

The shape

The subcategory name and its parent name both live in categories, so the query joins the table to itself: one alias plays the subcategory role, the other plays the parent role, and c.parent_id = p.id links them.

Clause by clause

SELECT c.name AS subcategory_name, p.name AS parent_category_name pulls name from both aliased instances. c.name is the child category (Phones, Men's, Furniture); p.name is the parent it rolls up to (Electronics, Clothing, Home & Garden).
FROM categories c reads the table in the subcategory role.
JOIN categories p ON c.parent_id = p.id reads the same table again in the parent role and links each row's parent_id to the matching row's id. The top-level categories (Electronics, Clothing, Home & Garden) have parent_id = NULL, so they never match anything in p and drop out of the result, which is what the prompt asks for.

The trap

The direction of the join condition matters. c.parent_id = p.id matches each child to its parent. Flipping it to c.id = p.parent_id reverses the roles: now p is the child and c is the parent, and the SELECT list returns parent names labeled as subcategory_name. Pick the alias that plays each role first, then write the condition in that direction.

You practiced a self-join on a parent-child column. The same shape that pairs employees with managers pairs subcategories with parent categories — the structure is identical, only the domain changes.

Return the subcategory name and parent category name for every subcategory that belongs to another category

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.