sql - SQLite: Self Join with WHERE Clause -

i have table used store hierarchy, references itself. in need of sql statement determine parent's node type. below have given structure of table sample data provide best explanation of trying figure out.

nodes table

create table if not exists nodes(id integer primary key autoincrement, type text not null, parent_id integer references nodes(id) on delete cascade

note: parent_id can null reference root node.

sample data

insert nodes(type, parent_id) values('grp', null); -- id: 1, title: hello, world! insert nodes(type, parent_id) values('txt', 1);    -- id: 2, title: print insert nodes(type, parent_id) values('rnd', 1);    -- id: 3, title: random output insert nodes(type, parent_id) values('txt', 3);    -- id: 4, title: output #1 insert nodes(type, parent_id) values('txt', 3);    -- id: 5, title: output #2 insert nodes(type, parent_id) values('txt', 3);    -- id: 6, title: output #3 

there titles each node, have listed in comment, usability put them in comments. looking have single sql statement return everything, output #* using parent's attributes.

my attempt

select id nodes parent_id not in (select id                         nodes                         type = 'rnd'); 

my attempt works part, since parent_id can null informed researching join better solution. cannot figure out how make self join work way want to.

this query joins table based on parent_id , displays fields node , parent node. because left join used, results include nodes parents, including root nodes.

because same table referenced twice in same query, alias must used distinguish between 2 tables. syntax "nodes parent" creates alias "parent".

select nodes.*, parent.* nodes left join nodes parent on nodes.parent_id = parent.id 

to find nodes parent type not equal "rnd" in query above, need add below clause query.

where parent.type != 'rnd' or parent.type null 

the important point here believe missing in above query way null values , comparison operators work together. result of null comparison operators , other value comes out false. why second condition needed in clause above. "is" keyword special keyword can used check null values.