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 >> 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 >> > _______________________________________________ > 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
