Retrieving Parent Hierarchy Entity in SQL Server and SQLite

sql serversqlite

I have the following table structure:

CREATE TABLE "Material" (
    "MaterialId" TEXT NOT NULL CONSTRAINT "PK_Material" PRIMARY KEY,
    "Content" TEXT NULL,
    "Title" TEXT NULL,
    "Paid" INTEGER NOT NULL,
    "Published" INTEGER NOT NULL,
    "ParentMaterialId" TEXT NULL,
    "Dates" TEXT NULL,
    "Private" INTEGER NOT NULL,
    CONSTRAINT "FK_Material_Material_ParentMaterialId" FOREIGN KEY ("ParentMaterialId") REFERENCES "Material" ("MaterialId") ON DELETE RESTRICT
)

Each material can have an infinite sub materials inside them, and i would like:

  1. Get the outer most ancestor material for a particular child.
  2. Get the First ancestor that is Paid (Paid is a boolean)

I am not that good at complex database operations, so please be kind.

Best Answer

Both SQLite and SQL Server understand enough standard SQL to do a recursive tree walk like this:

WITH AllAncestors AS (
  SELECT *
  FROM Material
  WHERE MaterialId = 12345

  UNION ALL

  SELECT parent.*
  FROM Material AS parent
  JOIN AllAncestors AS child ON child.ParentMaterialId = parent.MaterialId
)
SELECT *
FROM AllAncestors
WHERE ParentMaterialId IS NULL;

WITH AncestorsUntilPaid AS (
  SELECT *
  FROM Material
  WHERE MaterialId = 12345

  UNION ALL

  SELECT parent.*
  FROM Material AS parent
  JOIN AncestorsUntilPaid AS child ON child.ParentMaterialId = parent.MaterialId
  WHERE child.Paid = 0   -- stops before the parent of a paid child
)
SELECT *
FROM AncestorsUntilPaid
WHERE Paid = 1;
Related Question