i have 3 tables which i am trying to generate a pretty complex query...here
are the tables.

tblmap
formid int
inputid int
listorder int

tblresults
inputid int
respondantid int
respondandinput nvarchar

tblinput
inputid int
displayname

here is some sample data from the tables..

tblmap
1 1 1
1 2 2

tblresults
1 1 a
2 1 b
1 2 b
2 2 c

tblinput
1 test
2 ss


ok...now that we have that out of the way....here are the results i need..

for a given tblmap.formid i need

tblresults.respondantid,tblresults.respondantinput for each input id ordered
by tblmap.listorder
so the records would look like this for formid 1:

1 a b
2 b c


the number of inputids for any given form will be variable....


Thnks in advance for any help....


Craig Murray
CSM Consulting
www.csmconsulting.com
craig@csmconsulting.com

Not sure if this is what you want...

/*-------------------------------------------------------
This UDF returns all Respondant Input in a string by
formid and respondantid.
-------------------------------------------------------*/
CREATE FUNCTION UserFrmInput
(@formid int
,@respondantid int)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Input NVARCHAR(100)
DEcLARE @RespondantInputs NVARCHAR(1000)
DECLARE C_INPUT CURSOR
FOR
SELECT T1.respondantinput
FROM tblresults T1 INNER JOIN tblmap T2 ON T1.inputid = T2.inputid
WHERE T1.respondantid = @respondantid
AND T2.formid = @formid

OPEN C_INPUT
FETCH NEXT FROM C_INPUT INTO @Input
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RespondantInputs = ISNULL(@RespondantInputs,'') +
ISNULL(@Input,'')
FETCH NEXT FROM C_INPUT INTO @Input
END
CLOSE C_INPUT
DEALLOCATE C_INPUT
RETURN @RespondantInputs
END


SELECT T1.formid AS [Form ID]
,T2.respondantid AS [Respondant ID]
,dbo.UserFrmInput(T1.formid,T2.respondantid) [Respondant Input]
FROM tblmap T1 INNER JOIN tblresults T2 ON T1.inputid = T2.inputid
WHERE T1.formid = 1
GROUP BY T1.formid,T2.respondantid

HTH,
Aiden

[quoted text, click to view]