Many thanks that someone tried to resolve my problem. I have been looking for replies since i posted my question. The result is incorrect because when A is part of B and B is part of A so in this case if user either provide A group then all of B and its subGroups should be returned, same as if user provide B group as parameter then I have to traverse all subGroups of B and A in this case. I mean there is recursion but it won't break in loop like recursive CTP returned error as mentioned by one above.

Group B1(269) and B2(270) are part Group B(271)

Group B1(269) is also part of Group A(272)

Group A(272) is part of Group B(271)

Group B(271) is part of Group A(272)

Group C(273) and Group D(274) are independent

Your result

GroupId Groups

----------- -----------------

269 B1

270 B2

271 B,B1,B2,A

272 A,B1,B

273 C

274 D

the parameters are GroupId list comma separated provided by users as follows: and I have to put groupIds and their sub-group Ids till nth level (Does Not fail like CTE recusion cos its NOT a tree, its grouping of users and groups)

GroupIds Groups

----------- -----------------

269,271 B1,B2,B

270 B2

271 B,B1,B2,A

272 B,B1,B2,A (because B is part of A and A is part of B so all groups under B are part of A)

273 C

274 D

273,271 B1,B2,B,A,C

274,272 B1,B2,B,A,D

The function or SP will get a list of groupIds and i have to look their subgroups and groups till there is no any sub-groups in given groups till nth level and return list of groupIds. Further, i will then pass this list to groupusers table to pull users finally.

Hope you understand now. I think we can't solve it through single query yet. Plz. help

Shamshad Ali.