Quoth "Black, Michael (IS)" <michael.bla...@ngc.com>, on 2010-12-03 17:07:23
-0600:
> INSERT INTO "know" VALUES('a','b');
> INSERT INTO "know" VALUES('a','c');
> INSERT INTO "know" VALUES('a','d');
> INSERT INTO "know" VALUES('aa','b');
> INSERT INTO "know" VALUES('b','bb');
> INSERT INTO "know" VALUES('b','cc');
> INSERT INTO "know" VALUES('b','dd');
> INSERT INTO "know" VALUES('c','ee');
> INSERT INTO "know" VALUES('c','ff');
[...]
> What's missing is the a|aa|b or a|b|aa relationship
You're talking about a symmetrical binary relation, I gather. So that
means that R(a, b) = R(b, a) for all a, b---but a table doesn't know
that.
> It works if 'aa','b' is inserted as 'b','aa' -- but I'd like to not
> depend on the ordering -- or maintain alphabetical order.
This feels like a constraint out of nowhere. Why not?
Anyway, neither of those will work directly. An obvious approach
would be to use two rows for any non-reflexive entry in the relation,
which is a small amount of application logic. Another would be to
rewrite the query to union the two directions together, then probably
always insert non-reflexive entries in lexicographical order for
consistency (to create an invariant of one row per pairing). The
latter might be most easily done with a view of « SELECT a, b FROM t
UNION SELECT b, a FROM t » but I'm not sure how efficient it would be.
---> Drake Wilson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
