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?

What specifically about your query is not working as expected (I'm assuming it's not the obvious typo that would give a syntax error). Can you give an example where your query fails to give the correct result? — Mark Byers
– Mark Byers, Commented Jul 30, 2010 at 13:53

Mark Byers · Accepted Answer · 2010-07-30 13:49:58Z

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

answered Jul 30, 2010 at 13:49

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying a MySQL db representation of a tree

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related