【Question】
I have two tables:

[persons]
id | name
—+——-
1 | anna
2 | jake
3 | jhon
4 | jessica
5 | albert

[groups]
id | member
—+——-
1 | 1,3,4
2 | 2,4,5
3 | 1,2,3
4 | 4,5
5 | 1,6,7

I try to get people according to groups’s id 2 and 3 using

select id,name from persons
where id in (select member from groups where id in (2,3));

But only one record (id=2) is returned. Is there any suggestion?

【Answer】
You need to split groups table’s member field values to perform a set operation. A SQL workaround is complicated. An SPL (Structured Process Language) script is concise and easy-to-understand:

A3: Split each of the two specified member values into sequences and then union all of them together; the result is a sequence [1,2,3,4,5].

A4: Get records according to A3’s sequence.

split(), union() and pos() are set-related function. They can help make a set-related computation easier.

esProc provides JDBC interface that makes it function like a database. See How to Call an SPL Script in Java.