I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on.
The relation between the nodes in the tree structure is represented by table A:
Table A:
node_id integer
child_node_id integer
Table B represents the nodes in the tree:
Table B:
id integer
state boolean
The state of table B represents the states true = on and false = off.
Question:
I would like to select all the light bulbs which are turned off AND has all immediate children turned on.
This is probably very simple, but I can't seem to get my head around it.
Best Answer
I would first rethink the design. You only need one table:
With the layout as presented in the question, the query could be:
This includes nodes that are turned off and have no children at all.
To exclude nodes without children, replace the first
LEFT JOIN
with a plainJOIN
.Or, may be faster: