We have a child-parents relationship in two tables:


 children table:


CREATE TABLE child (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `direct_parent_id` int(11) DEFAULT NULL,
  KEY `direct_parent_id_child_fk` (`direct_parent_id`),
  CONSTRAINT `direct_parent_id_child_fk` FOREIGN KEY (`direct_parent_id`) REFERENCES `child` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

parents table:

CREATE TABLE `parents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `child_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `brand_id_parent_id_unx` (`child_id`,`parent_id`),
  KEY `parents_child_fk_idx` (`child_id`),
  KEY `parents_child_fk_2_idx` (`parent_id`),
  CONSTRAINT `parents_brand_fk` FOREIGN KEY (`child_id`) REFERENCES `child` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `brand_tree_brand_fk_2` FOREIGN KEY (`parent_id`) REFERENCES `child` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

This table connects each child with:

1- itself

2- its direct parent

3- all parents of its parents

Can we write SQL query (or more than one query) to find the children ids which don’t have the right data in parents table based on the children table? 


For example: the child id 300 
have the direct parent 200, so all top parents will be parents for 300, 
we should have these (child_id -> parent_id) records in the parents table:


 300 -> 300

300 -> 200

300 -> 100

If one of them is not exist, or if id:300 has more records (for example 300 -> 400) 
we want to return id:300 in our result

CodePudding user response：

Use recursive query to find all combinations

create table test0719 
select 100 id, 100 pid
union all select 200 id, 100 pid
union all select 300 id, 200 pid
;

with recursive t1(id, pid, rootid) as(
select id, pid, id from test0719
union all
select t2.id, t2.pid, t1.rootid from test0719 t2, t1
 where t1.pid = t2.id
   and t1.id != t2.pid
)
select distinct v1.child_id from parents_table v1
 where concat(v1.child_id, '_', v1.parent)
         not in (select concat(t1.rootid, '_', t1.id) from t1 t1)
;

CodePudding user response：

CREATE TABLE Person (
    PersonId int         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    Name     varchar(16) NOT NULL
)

INSERT INTO Person (Name)
VALUES ('Child'), ('Parent-1'), ('Parent-2'), ('Grandparent-1-1'), ('Grandparent-1-2'), ('Grandparent-2-1'), ('Grandparent-2-2')

CREATE TABLE Relationship (
    PersonId int NOT NULL,
    ParentId int NOT NULL, -- If a person doens't have a person then they're not in this table.

    CONSTRAINT Relationship_PK               PRIMARY KEY CLUSTERED (PersonId, ParentId),
    CONSTRAINT Relationship_FK_Person_Person FOREIGN KEY (PersonId) REFERENCES Person (PersonId),
    CONSTRAINT Relationship_FK_Person_Parent FOREIGN KEY (ParentId) REFERENCES Person (PersonId),
    -- In a simple heirarchy where there is only one parent the you'd add an FK (ParentId) REFERENCES Relationship (PersonId).
)

INSERT INTO Relationship (PersonId, ParentId)
SELECT c.PersonId, p.PersonId
FROM Person c CROSS JOIN Person p
WHERE c.Name = 'Child'
    AND p.Name LIKE 'Parent%'

INSERT INTO Relationship (PersonId, ParentId)
SELECT p.PersonId, g.PersonId
FROM Person p CROSS JOIN Person g
WHERE p.Name LIKE 'Parent%'
    AND g.Name LIKE 'Grand'   p.Name   '%'

GO

CREATE VIEW DescendantOrSelf
AS
    WITH cteR AS (
        -- Base case: generation zero is yourself.
        SELECT p.PersonId, p.PersonId AS DescendantId, 0 AS Generation
        FROM Person p

        UNION ALL

        -- Induction case: get the descendants.
        SELECT cte.PersonId, r.PersonId AS DescendantId, cte.Generation   1 AS Generation
        FROM cteR cte
            INNER JOIN Relationship r ON r.ParentId = cte.DescendantId
    )
    SELECT PersonId, DescendantId, Generation
    FROM cteR

GO

SELECT p.PersonId, p.Name, d.PersonId AS DescendantId, d.Name AS DescendantName, Generation
FROM Person p
    INNER JOIN DescendantOrSelf r ON p.PersonId = r.PersonId
    LEFT JOIN Person d ON r.DescendantId = d.PersonId
ORDER BY p.PersonId

When you have a recursive hierarchical relationship like this you always need to write a VIEW to unroll it, and if you're using EntityFramework then use OnModelCreating to map the view to a class to allow querying from LINQ; the condition WHERE Generation <> 0 allows you to easily include/exclude the self case.