Re: [sqlite] joining two sequences?
Hi Jim, On Nov 20, 2010, at 6:52 PM, Jim Morris wrote: This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not sure this is the most efficient way. Clever :) Thank you very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] joining two sequences?
Slightly better version: select distinct f1 .key as foo_key, b1.key as bar_key from bar b1 inner join foo f1 on f1 .value = b1.value where not exists ( -- Values for a particular key in foo select f3.value from foo f3 WHERE f3.key= f1.key union -- Values for a particular key in bar select b3.value from bar b3 WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 6:40 PM, Jim Morris wrote: This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not sure this is the most efficient way. select distinct f1 .key as foo_key, b1.key as bar_key from bar b1 inner join foo f1 on f1 .value = b1.value where not exists ( -- Values for a particular key in foo select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value WHERE f3.key= f1.key union -- Values for a particular key in bar select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 1:03 PM, Petite Abeille wrote: 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 fromfoo group bykey ) as foo join( select key, group_concat( value ) as value frombar group bykey ) 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] joining two sequences?
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 fromfoo group bykey ) as foo join( select key, group_concat( value ) as value frombar group bykey ) 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
Re: [sqlite] joining two sequences?
This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not sure this is the most efficient way. select distinct f1 .key as foo_key, b1.key as bar_key from bar b1 inner join foo f1 on f1 .value = b1.value where not exists ( -- Values for a particular key in foo select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value WHERE f3.key= f1.key union -- Values for a particular key in bar select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 1:03 PM, Petite Abeille wrote: 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 fromfoo group bykey ) as foo join( select key, group_concat( value ) as value frombar group bykey ) 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
