Mysql – How to create a view with self referencing table and a cyclic condition

MySQLview

I have a self-referencing table.

ID BIGINT NOT NULL PRIMAY KEY,
DELETED TIMESTAMP NULL,
PARENT_ID FK

How can I create a view of this table which each row's DELETED is NULL and any of its parent's DELETED is also NULL?

So far, I tried and it doesn't work as expected.

CREATE VIEW `OPERATOR_` AS
SELECT c.* from OPERATOR AS c LEFT OUTER JOIN OPERATOR AS p ON c.PARENT_ID = p.ID
WHERE c.DELETED_ IS NULL AND p.DELETED_ IS NULL;

example 1

ID    DELETED   PARENT_ID
0     2017...   NULL         NOT SELECTED
1     NULL      0            NOT SELECTED
2     NULL      1            NOT SELECTED

example 2

ID    DELETED   PARENT_ID
0     NULL      NULL             SELECTED
1     2017...   0            NOT SELECTED
2     NULL      1            NOT SELECTED

example 3

ID    DELETED   PARENT_ID
0     NULL      NULL             SELECTED
1     NULL      0                SELECTED
2     2017...   1            NOT SELECTED

Best Answer

SELECT *
    FROM tbl a
    WHERE deleted IS NULL
      AND EXISTS ( SELECT * FROM tbl 
                       WHERE id = a.parent_id
                         AND deleted IS NULL );

See if that works, then turn it into a VIEW.

Related Question