Query to find group members

Posted by Thiru under Sql Server on 7/26/2011 | Points: 10 | Views : 1755 | Status : [Member] | Replies : 4
Hi,
I like to get list of group members using a single query - Please help me to get a solution.

Here is table structure and data:
id	name	team
101 A 102,104
102 B 103,105
103 C
104 D
105 E


from the above table data i need to know the selected id's team
for example: to find team of A
we should get the the following result:
id	name	team
102 B 103,105
104 D


note: data in the field team is stored exactly same as above (i mean using coma separated)

expecting your valuable reply in this regard.
Regards,
Thiru




Responses

Posted by: Jpchoudhari on: 7/26/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Create this function first


Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))        

returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end


And then use it like this

Select * from tbl_Name Where ID In
(Select items from Split((Select team From tbl_Name Where [name]='A'),','))

Find attached script
 Download source file

Kind Regards,
Jay

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 7/26/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Jaydeep,
Thanks for your reply.
Hope you are using procedure - for your information i have no experience in using procedures.
more over i am using mysql db and in need of a query for mysql db.
can you please get me a solutions for this ?

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jpchoudhari on: 7/26/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

As far as i know all the sql statements are available to MySql db also might with some difference.

Check out for Create Function Syntax else wise select statement is valid for MySql as it is.



Kind Regards,
Jay

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 9/6/2011 [Member] Platinum | Points: 25

Up
0
Down
Try this

--Environment setup

Declare @t table(id int,name varchar (10),team varchar(10))

Insert into @t Values
(101,'A','102,104')
,(102,'B','103,105')
,(103,'C','')
,(104,'D','')
,(105,'E','')
Declare @TeamName As varchar(10) = 'A'


--Query

;With Cte1 As(

SELECT F1.id,
F1.name,
F1.team,
O.SplitData
FROM
(
SELECT *,
cast('<X>'+replace(F.team,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F
)F1
CROSS APPLY
(
SELECT fdata.D.value('.','varchar(50)') as SplitData
FROM f1.xmlfilter.nodes('X') as fdata(D)
) O
)

Select distinct c1.id,c1.name,c1.team
From Cte1 c1
join (Select SplitData From Cte1 Where name = @TeamName) x
on c1.id = x.SplitData


-- Result

 id	name	team

102 B 103,105
104 D


Hope this helps

Best Regards,
Niladri Biswas

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response