sql - Display Parent-Child relationship when Parent and Child are stored in same table

Sql - Display Parent-Child relationship when Parent and Child are stored in same table

To display a parent-child relationship when both parent and child records are stored in the same table, you typically use a self-referential foreign key. This type of relationship is common in hierarchical data structures such as organizational charts, categories in e-commerce sites, etc.

Assume you have a table named Categories with the following structure:

  • CategoryID: Unique identifier for each category (Primary Key).
  • CategoryName: Name of the category.
  • ParentCategoryID: References CategoryID of the parent category (Foreign Key).

Example Table Structure

CREATE TABLE Categories ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(255), ParentCategoryID INT, FOREIGN KEY (ParentCategoryID) REFERENCES Categories(CategoryID) );

Sample Data

INSERT INTO Categories (CategoryID, CategoryName, ParentCategoryID) VALUES (1, 'Electronics', NULL), (2, 'Computers', 1), (3, 'Laptops', 2), (4, 'Desktops', 2), (5, 'Smartphones', 1), (6, 'Cameras', 1), (7, 'DSLR', 6), (8, 'Mirrorless', 6);

Query to Display Parent-Child Relationships

You can use a recursive common table expression (CTE) to display the hierarchy of categories. Here's how to write such a query in SQL:

WITH RECURSIVE CategoryHierarchy AS ( -- Anchor member: start with the top-level categories (those with no parent) SELECT CategoryID, CategoryName, ParentCategoryID, CategoryName AS HierarchyPath FROM Categories WHERE ParentCategoryID IS NULL UNION ALL -- Recursive member: join with the parent categories to build the hierarchy SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, CONCAT(ch.HierarchyPath, ' > ', c.CategoryName) AS HierarchyPath FROM Categories c INNER JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID ) -- Final selection SELECT CategoryID, CategoryName, ParentCategoryID, HierarchyPath FROM CategoryHierarchy;

Explanation

  1. Anchor Member:

    • Selects the top-level categories where ParentCategoryID is NULL.
    • Initializes the hierarchy path with the category name.

  2. Recursive Member:

    • Joins the Categories table with the CategoryHierarchy CTE on the ParentCategoryID.
    • Builds the HierarchyPath by concatenating the parent's hierarchy path with the current category name.

  3. Final Selection:

    • Selects the columns from the CTE to display the hierarchy.

Result

This query will output the hierarchy of categories with their paths:

| CategoryID | CategoryName | ParentCategoryID | HierarchyPath | |------------|--------------|------------------|-------------------------| | 1 | Electronics | NULL | Electronics | | 2 | Computers | 1 | Electronics > Computers | | 3 | Laptops | 2 | Electronics > Computers > Laptops | | 4 | Desktops | 2 | Electronics > Computers > Desktops | | 5 | Smartphones | 1 | Electronics > Smartphones | | 6 | Cameras | 1 | Electronics > Cameras | | 7 | DSLR | 6 | Electronics > Cameras > DSLR | | 8 | Mirrorless | 6 | Electronics > Cameras > Mirrorless |

This output provides a clear hierarchy of the parent-child relationships within the Categories table, displaying how each category fits within the overall structure.

Examples

  1. SQL query to display Parent-Child relationship using self-join:

    • Description: This query utilizes a self-join to display the Parent-Child relationship when both Parent and Child records are stored in the same table.
    SELECT parent.*, child.* FROM table_name parent LEFT JOIN table_name child ON parent.parent_id = child.parent_id;

  2. SQL query to display Parent-Child hierarchy using Common Table Expression (CTE):

    • Description: This query utilizes a Common Table Expression (CTE) to recursively retrieve Parent-Child relationships, assuming a hierarchical structure.
    WITH RECURSIVE ParentChildHierarchy AS ( SELECT parent_id, child_id, parent_name, child_name FROM table_name WHERE parent_id IS NULL UNION ALL SELECT t.parent_id, t.child_id, t.parent_name, t.child_name FROM table_name t JOIN ParentChildHierarchy pch ON t.parent_id = pch.child_id ) SELECT * FROM ParentChildHierarchy;

  3. SQL query to display Parent-Child relationship using INNER JOIN:

    • Description: This query utilizes INNER JOIN to display the Parent-Child relationship when both Parent and Child records are stored in the same table.
    SELECT parent.*, child.* FROM table_name parent INNER JOIN table_name child ON parent.parent_id = child.parent_id;

  4. SQL query to display Parent-Child relationship with indentation for hierarchy:

    • Description: This query displays the Parent-Child relationship with indentation to represent the hierarchical structure visually.
    SELECT RPAD('', (LEVEL - 1) * 2, ' ') || child_id AS child_id, RPAD('', (LEVEL - 1) * 2, ' ') || child_name AS child_name, RPAD('', (LEVEL - 1) * 2, ' ') || parent_id AS parent_id, RPAD('', (LEVEL - 1) * 2, ' ') || parent_name AS parent_name FROM table_name START WITH parent_id IS NULL CONNECT BY PRIOR child_id = parent_id;

  5. SQL query to display Parent-Child relationship using LEFT JOIN:

    • Description: This query utilizes LEFT JOIN to display all Parent records along with their corresponding Child records when both are stored in the same table.
    SELECT parent.*, child.* FROM table_name parent LEFT JOIN table_name child ON parent.parent_id = child.parent_id;

  6. SQL query to display Parent-Child relationship with level numbering:

    • Description: This query displays the Parent-Child relationship with level numbering to represent the depth of the hierarchy.
    SELECT child_id, child_name, parent_id, parent_name, LEVEL AS hierarchy_level FROM table_name START WITH parent_id IS NULL CONNECT BY PRIOR child_id = parent_id;

  7. SQL query to display Parent-Child relationship ordered by Parent-Child hierarchy:

    • Description: This query displays the Parent-Child relationship ordered by the Parent-Child hierarchy, assuming a hierarchical structure.
    SELECT parent.*, child.* FROM table_name parent LEFT JOIN table_name child ON parent.parent_id = child.parent_id ORDER BY parent.parent_id, child.child_id;

  8. SQL query to display Parent-Child relationship using subquery:

    • Description: This query utilizes a subquery to display the Parent-Child relationship when both are stored in the same table.
    SELECT parent.*, (SELECT * FROM table_name child WHERE child.parent_id = parent.parent_id) AS child FROM table_name parent WHERE parent.parent_id IS NOT NULL;

  9. SQL query to display Parent-Child relationship with additional hierarchical columns:

    • Description: This query displays the Parent-Child relationship along with additional columns representing the hierarchical level and path.
    SELECT SYS_CONNECT_BY_PATH(child_id, '/') AS path, LEVEL AS level, parent_id, child_id, parent_name, child_name FROM table_name START WITH parent_id IS NULL CONNECT BY PRIOR child_id = parent_id;

  10. SQL query to display Parent-Child relationship with recursive query:

    • Description: This query uses a recursive common table expression (CTE) to display the Parent-Child relationship, assuming a hierarchical structure.
    WITH RECURSIVE ParentChildCTE AS ( SELECT parent_id, child_id, parent_name, child_name FROM table_name WHERE parent_id IS NULL UNION ALL SELECT t.parent_id, t.child_id, t.parent_name, t.child_name FROM table_name t JOIN ParentChildCTE pch ON t.parent_id = pch.child_id ) SELECT * FROM ParentChildCTE;

More Tags

activity-finish autoscaling lexicographic google-kubernetes-engine docker-volume window.open websecurity angular-data s3cmd hikaricp

More Programming Questions

More Electrochemistry Calculators

More Dog Calculators

More Bio laboratory Calculators

More Fitness Calculators