Hi, Thanks, and i found another alternative query.
SELECT a.name AS PERSON_NAME, c.name AS LANGUAGUE_NAME FROM `person1` a LEFT JOIN SYSTEM_RANGE(1, SELECT MAX(id) FROM LANGUAGE1) b ON ( true ) INNER JOIN `LANGUAGE1` c ON ( c.id = ARRAY_GET( a.speak, b.X ) ) PERSON_NAME LANGUAGUE_NAME abc a abc b abc c Regards, kinfei On Aug 26, 12:03 pm, "Thomas Mueller" <[EMAIL PROTECTED]> wrote: > Hi, > > Sorry, this is currently not supported. However you could solve this > problem by implementing your own custom Java method. Example: > > create table person(id bigint, name varchar,speak array); > insert into person values(1, 'abc', (1,2,3)); > create table language(id bigint, name varchar); > insert into language values(1, 'a'), (2, 'b'), (3, 'c'); > CREATE ALIAS ARRAY_IN FOR "com.acme.Util.arrayIn"; > SELECT b.* FROM person a INNER JOIN language b ON ARRAY_IN(b.id, a.speak); > > You would need to write com.acme.Util, Boolean arrayIn(Object a, Object[] b). > > I think that would be useful for others as well. Please tell me if you > want to do that, I would be interested in including the code in H2. > > Regards, > Thomas > > On Sat, Aug 23, 2008 at 8:56 AM, kinfei <[EMAIL PROTECTED]> wrote: > > > Let say, > > table > > person ( id bigint, name varchar, speak array ); > > 1, 'abc', (1,2,3) > > > language (id bigint, name varchar ); > > 1, 'a' > > 2, 'b' > > 3, 'c' > > > SELECT b.* FROM `person` a > > INNER JOIN `languague` b > > ON ( b.id IN a.speak ) > > > Is that possible? Any idea? > > ( b.id IN a.speak ) =e.g ( b.in IN ( 1, 2, 3 ) ) -- 3 rows; > > (possible?) > > ( b.id IN ( a.speak ) ) =e.g ( b.in IN ( ( 1, 2, 3 ) ) ) --- 0 row; > > > ( b.id IN ( a.speak ) ) return 0 row because b.id is not an array type > > * ( b.id IN ( ( 1, 2, 3 ), ( 2,3,3 ) ) ) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
