TL;DR: Use a JOIN.

I've got a table representing a tree, using two columns:

CREATE TABLE "EXAMPLE" (
    "PROP_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "PARENT_PROP_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    /*
      * Data ...
      */
    "CLASS_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE
)

and I want to select all children for parents whose CLASS_ID have a certain value (e.g. abcd).

The way I could do it was via a JOIN statement:

SELECT * FROM EXAMPLE child
  JOIN EXAMPLE parent on (child.parent_prop_id = parent.prop_id)
  WHERE parent.class_id = 'abcd';

This will:

  • Perform a join where child.parent_prop_id = parent.prop_id
  • Filter only parent.class_id = 'abcd'

Simple.

Bonus

This will work with other databases as well :)

HTH,