Querying a MySQL db representation of a tree

Question

I have two tables that represent parent/child nodes, their types, and their relationships:

Table 1

|  nodeID  |  node  |  name  |  type    |
|   1      |   A    |  test  |  Type A  |
|   2      |   B    |  abcd  |  Type B  |
|   3      |   C    |  defg  |  Type C  |

Table 2

|  parentNodeID  |  childNodeID  |
|      1         |       2       |
|      1         |       3       |

I would like to write a query where I find children nodes of Type B that do not have a parent of Type C.

I've tried

select node from table1 t1 left join table2 t2 on t1.nodeID=t2.childNodeID
  where Type="Type B"
    and t2.parentNodeID not in (select nodeID from t1 where type="Type C) 

This is not working as expected. Am I doing this correctly? Is there an easier way?

Answer 1

You could try this:

SELECT node
FROM Table1
WHERE type = 'Type B'
AND nodeID NOT IN (
   SELECT T2.childNodeID
   FROM table1 T1
   JOIN table2 T2
   ON T2.parentNodeId = T1.nodeId
   WHERE T1.type = 'Type C'
)

It means something like this: Find all nodes of type b that are not one of (the child nodes that have a parent node of type c).