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
NULL and any of its parent's
DELETED is also
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;
ID DELETED PARENT_ID 0 2017... NULL NOT SELECTED 1 NULL 0 NOT SELECTED 2 NULL 1 NOT SELECTED
ID DELETED PARENT_ID 0 NULL NULL SELECTED 1 2017... 0 NOT SELECTED 2 NULL 1 NOT SELECTED
ID DELETED PARENT_ID 0 NULL NULL SELECTED 1 NULL 0 SELECTED 2 2017... 1 NOT SELECTED