team.bernard - Sunday, October 22, 2017 5:41 AMHello,I'm working on an electrical network, edge are link between nodes, insert lines with 'end' are all clients.
The first line is the main generator.My tree is like this :
CREATE TABLE tree
( edge varchar(5),
from_node integer,
to_node integer,
mode character varying(5));INSERT INTO tree(edge, from_node, to_node, mode) VALUES
('A', 1, 4, 'start'),
('B', 4, 2, 'end'), ('C', 5, 3, 'end'),
('D', 4, 5, NULL),
('E', 6, 5, NULL), ('F', 6, 7, 'end');The issue is that the 'E' line is wrong because it should be 5, 6.
Do not take node number as a reference, because in real life, these as varchar like : CODEXXX and increment
Is it possible to help me the query that will return all mismatch start and endpoints ?
Regards
The logic is pretty simple for such a DAG (Directed Acyclic Graph). The FROM node column should be unique. Any non-unique value is a candidate for possibly being an error.
You should also add a self-referencing FK stipulating that no TO node can exist unless it first exists as a FROM node. That also means that you're listing your nodes backwards if the source of electricity starts at node 1.
To wit, the nodes should be listed as follows as FROM/TO because node 1 is the source of power and should be the root of the tree.
1,NULL
4,1
2,4
5,4
3,5
6,5
7,6
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)