
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
EmoticonEmoticon