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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
