Hello,
I am having trouble grasping the following issue intuitively --
sqlite> select * from node limit 3;
node_id node_type node_name node_confidence
-- -- - ---
1 ACT Mike Garrison 0.99932
2 ACT Pat Cox0.999193
3 ACT Steve Weise0.999149
sqlite> select count(*) from node;
count(*)
--
17046
sqlite> select * from edge limit 3;
edge_id from_node to_node edge_confidence edge_type
-- -- -- --- --
1 Barry Webster 0.998918 aa
2 Barry Wright 0.998896 aa
3 Barton Webster 0.998561 aa
sqlite> select count(*) from edge;
count(*)
--
19252
sqlite> select e.edge_id, n1.node_id AS from_node_id
...> from edge e join node n1 on
...> e.from_node = n1.node_name limit 3;
edge_id from_node_id
--
1 3840
2 3840
3 7230
sqlite> select count(*) from edge e join node n1 on e.from_node = n1.node_name;
count(*)
--
21632
sqlite>
My intuition says that the row count from the last select should not
be more than 19,252, the count of the left table (edge) in the join,
and, in fact, can be less as not all e.from_node may find a match in
n1.node_name. Instead, I am getting 21,632. In fact, when I create
another join using e.to_node, I get even more records as below, and I
am just not able to grasp why this is happening.
sqlite> select count(*) from edge e join node n1
...> on e.from_node = n1.node_name join
...> node n2 on e.to_node = n2.node_name;
count(*)
--
25277
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users