Tutorial :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?


Solution: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).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »