CODEWITHSUNDEEP
mysql
CoolGravatar
CoolGravatar

Reputation: 5478

Querying a database representation of a tree (MySQL)

I have two tables, one that contains nodes and second that contains the relationships between them like so:

Table A (named Node)

NodeID | NodeName 
 100   |  Name 1
 101   |  Name 2
 102   |  Name 3

Table B (named Relationship)

NodeParent | NodeChild
  100      |    101
  101      |    102

I need to execute a query that needs to find which nodes are orphans (they are not in the Relationship table). How can I search through NodeParent and NodeChild at the same time?

Upvotes: 1

Views: 360

Answers (2)

Mchl
Mchl

Reputation: 62369

SELECT 
  n.NodeID 
FROM 
  Node AS n 
LEFT JOIN 
  Relationship AS r
ON
  n.NodeID = r.NodeChild
WHERE
  r.NodeChild IS NULL

Upvotes: 2

ceteras
ceteras

Reputation: 3378

This will give you all nodes that have no children in Relationship table.

select n.*
from Node n
left join Relationship r on r.NodeChild = n.NodeID
where r.NodeChild is null;

Upvotes: 1

Related Questions