Hello Group,

Given these two tables:

pathlist
==========
id pathname
1 /folder/
2 /folder/sub1/
3 /folder/sub2/
4 /folder/sub1/sub1
5 /folder/sub2/sub1/
6 /folder/sub3/

pathgroup
===========

groupid basepathid
1 2
1 6
2 6
3 3


Is it possible to write a query to return all the pathlist.pathname
that belong to the bases listed in pathgroup.basepathid ?

Doing it in two queries and using a program:

group_path_list = SELECT pathlist.pathname FROM pathgroup, pathlist
WHERE pathgroup.id=1 AND pathlist.id=pathgroup.basepathid

-> Returns: /folder/sub3/ and /folder/sub1/

Expand the 'group_path_list' into strings that fit as an SQL
condition:

SELECT * FROM pathlist WHERE
SUBSTRING(pathlist.pathname, 1, len(group_path_list[0])) =
group_path_list[0]
OR
SUBSTRING(pathlist.pathname, 1, len(group_path_list[1])) =
group_path_list[1]


Which should return pathlist.id = {2,4,6}

Can this be done using SQL alone and in one query (with nested
queries) perhaps?

On 2010-10-14 18:47, lallous wrote:
You can find the "children" of 2 and 6 like:

select y.id
from pathlist x
join pathlist y
on y.pathname like x.pathname || '%'
where x.id in (2,6);

replace the in predicate with a join against pathgroup and you're home

/Lennart

[...]