Need Help to Develop Query: [modified]

[Shahzad Aslam]

Hi i have following tables Table:Messages, Fields: msg_id,message Table:Msg_groups, Fields:msg_id,Group now i want to select msg_id and group, a msg_id can have more than one group it should display like this: msg_id....group 123.......22,23,24 222.......22 122.......23,24 Any help will be appreciated. Shahzad Aslam -- modified at 5:37 Tuesday 27th March, 2007

[hamid_m]

the below query select groups for each msg_id SELECT Messages.msg_id, Msg_groups.Group FROM ( Messages INNER JOIN Msg_groups ON Messages.msg_id=Msg_groups.msg_id ) enjoy it ;)

[Krish KP]

can write a function which accepts "msgid" and fatches all "group" returns concatenated group. using select msgid, fugroup(msgid) ...... will give output as required.

Regards KP

[Shahzad Aslam]

can u help to develop the fugroup(msgid) function Shahzad Aslam

[Krish KP]

CREATE FUNCTION fnRegions (@msgid int) RETURNS VARCHAR(100) AS BEGIN DECLARE c1 CURSOR FOR SELECT DISTINCT CAST(groupid AS VARCHAR(40)) FROM msg_groups WHERE msg_id = @msgid DECLARE @row VARCHAR(50) DECLARE @retval VARCHAR(50) OPEN c1 FETCH c1 INTO @row SET @retval = '' WHILE @@FETCH_STATUS = 0 BEGIN SET @retval = @retval + @reg + ', ' FETCH c1 INTO @row END RETURN @retval END

Regards KP

[Shahzad Aslam]

its great Thanx!! Shahzad Aslam

[Krish KP]

wc

Regards KP