Hello, Given two tables describing sequences of key value pairs, what would be a reasonable way to join them?
For example, assuming two table foo and bar with identical structure: create temporary table foo ( key integer not null, value text not null, constraint foo_pk primary key( key, value ) ); create temporary table bar ( key integer not null, value text not null, constraint bar_pk primary key( key, value ) ); And a set of sequences in each of the table: insert into foo values( 1, 'a' ); insert into foo values( 2, 'a' ); insert into foo values( 2, 'b' ); insert into foo values( 3, 'a' ); insert into foo values( 3, 'b' ); insert into foo values( 3, 'c' ); insert into bar values( 4, 'a' ); insert into bar values( 4, 'b' ); What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 4, 'b' )? In other words, join the sequences with the same values? Right now, I'm using group_concat to flatten the sequences: select * from ( select key, group_concat( value ) as value from foo group by key ) as foo join ( select key, group_concat( value ) as value from bar group by key ) as bar on bar.value = foo.value Which results in: key|value|key|value 2|a,b|4|a,b All good, if perhaps clunky. But the documentation for group_concat mention that the order of the concatenated elements is arbitrary [1]. Which perhaps would preclude group_concat from being reliably use as a join predicate, no? Could someone think of a nice alternative to group_concat to join such data structure? Thanks in advance. Cheers, PA. [1] http://www.sqlite.org/lang_aggfunc.html _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users