How to get a value from the same table in another record using a parent id in a where clause

Question

In a treestructure i want to lookup a value in the parent record to see if it has a value in a field called nodOpen (Can be 0 for closed and 1 for Open). If the Parent-records field nodOpen is 1 then current record is visible and vice versa.

strSQL = "SELECT nodLevel, nodNum, nodLead, etc., nodParent FROM tblNode WHERE PARENTNODE IS OPEN Visible= 1 ORDER BY nodSort"

Is that possible in one SQL-sentence?

Answer 1

Let's assume that the table has the following structure.

create table tblNode (
    nodNum int not null,
    nodLevel int,
    nodOpen int not null,
    nodSort int not null,
    nodLead int not null,
    nodParent int null,
    -- insert other columns here(?)
    constraint PK_tblNode primary key clustered (nodNum),
    constraint FK_tblNode_tree foreign key (nodParent)
        references tblNode(nodNum)
);

The query would look something like this.

select 
    nodLevel, 
    nodNum, 
    nodLead, 
    nodParent, 
    nodSort -- you may have to include this column to sort on it later
    /* insert other columns here(?) */ 
from tblNode as childNode
inner join tblNode as parentNode on parentNode.nodNum = childNode.nodParent
where parentNode.nodOpen = 1
order by nodSort