title: SQLite: Self Join with WHERE Clause
body: <p>I have a table that is used to store a hierarchy, which references itself. I am in need of a SQL statement that will determine the parent's node type. Below I have given the structure of my table as well as sample data to provide the best explanation of what I am trying to figure out.</p>
  
  <p><strong>NODES Table</strong></p>
  
  <p><code>CREATE TABLE IF NOT EXISTS NODES(id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT NOT NULL, parent_id INTEGER REFERENCES NODES(id) ON DELETE CASCADE</code></p>
  
  <p><em>NOTE: <code>parent_id</code> can be NULL to reference the ROOT node.</em></p>
  
  <p><strong>Sample Data</strong></p>
  
  <pre><code>INSERT INTO NODES(type, parent_id) VALUES('GRP', NULL); -- id: 1, title: Hello, World!
  INSERT INTO NODES(type, parent_id) VALUES('TXT', 1);    -- id: 2, title: Print
  INSERT INTO NODES(type, parent_id) VALUES('RND', 1);    -- id: 3, title: Random Output
  INSERT INTO NODES(type, parent_id) VALUES('TXT', 3);    -- id: 4, title: OUTPUT #1
  INSERT INTO NODES(type, parent_id) VALUES('TXT', 3);    -- id: 5, title: OUTPUT #2
  INSERT INTO NODES(type, parent_id) VALUES('TXT', 3);    -- id: 6, title: OUTPUT #3
  </code></pre>
  
  <p>There are titles for each node, which I have listed in the comment, but for usability I just put them in the comments. What I am looking to do is have a single SQL statement to return everything, but the OUTPUT #* using the parent's attributes.</p>
  
  <p><strong>My Attempt</strong></p>
  
  <pre><code>SELECT id
  FROM NODES
  WHERE parent_id NOT IN (SELECT id
                          FROM NODES
                          WHERE type = 'RND');
  </code></pre>
  
  <p>My attempt works for the most part, but since parent_id can be NULL I was informed by researching this that a <code>JOIN</code> would be a much better solution. I just cannot figure out how to make a SELF JOIN work the way I want it to.</p>