0

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
  • Can you please add some sample data? – Arulkumar Jul 13 '16 at 6:20
0
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.

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.