...

Advanced Set-based Operations

 
SQL(179)

【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:

A

1

$select * from persons

2

$select * from groups where   id in(2,3)

3

=A2.union(member.split@pc())

4

=A1.select(A3.pos(id))

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.

 


SQL(179)
 
 •  305 View  •