I need a bit of SQL expertise here...I'm close but not close enough and joins
kind of confuse me.
Given the following table of 1-degree separations I want to get the 2-degree
separations
CREATE TABLE know(p1 varchar,p2 varchar);
CREATE INDEX know_idx on know(p1,p2);
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');
SELECT know.p1, know.p2, know_1.p2 from (know inner join know as know_1 on
know.p2=know_1.p1) where (know_1.p2<>know.p1);
a|b|bb
a|b|cc
a|b|dd
a|c|ee
a|c|ff
aa|b|bb
aa|b|cc
aa|b|dd
What's missing is the a|aa|b or a|b|aa relationship
It works if 'aa','b' is inserted as 'b','aa' -- but I'd like to not depend on
the ordering -- or maintain alphabetical order. Or do I need to be more
careful in checking that 'b' is already in the table so should be in p1 instead
of p2?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
