1

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.

1
  • Am currently testing with SQLite but will latter switch to sql-server – McKabue Nov 25 '18 at 18:46
2

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;
2
  • would you help me convert this into MySql? version: '5.7.24' – McKabue Dec 13 '18 at 19:18
  • with MySql, adding the word RECURSIVE after WITH works on version 8.0 mysqlserverteam.com/… – McKabue Dec 14 '18 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.