Query to find group members

Posted by Thiru under Sql Server on 7/26/2011 | Points: 10 | Views : 2800 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

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

Latest Posts