Combine Master Detail resultset into single row

[pflangan]

Does anyone know how to return a master-detail query in a single row. I know how to link through a form/subform or even a report/subreport
Example. I have a student enrollment database table

students
--------
sid data:1
student dataeter

classes
-------
cid data:1 data:2
class data:english data:math

courses
-------
sid data:1 data: 1
cid data:1 data: 2

so from this, peter attends math and english

what i want on a single row of my query is
name | courses
peter | math,english

i know i can do this using vb code, a for/while loop etc,
but is there any way to roll this into a single query
like:
select student,[mystery fn](select classes.class from classes inner join course on classes.cid=course.cid inner join students on course.sid=students.sid) [/color]

 

[TheBugSlayer]

Hmmm, if you use a join I guess you will get a row for each combination of student and course. You may have to build a recordset of courses for all students, combine those courses into a string in another field (in another table would be easier) and finally join to that table in your query to retrieve the courses as one field...Just an idea.

 

[pflangan]

Thanks, but that's a little too much vb code than what i'm looking for.

 

[LogicalmanUS]

pflanagan,

Unfortunately, what you are asking for is unrealistic in a sigle pass, as it requires you t concatonate records together, and even a Pivot Table can't do that...yet!

The use of a SProc would be the way to go, or, at the very least, your 'mystery fn' would be a UDF. Either way, it's code I'm afraid.

Here's the SProc code:

-- User SProc
-- Created by Logicalman 9/7/03 for TekTips
--
CREATE PROCEDURE [dbo].[usp_GET_CLASSES]
@SID int -- This is the Student ID to get classes for
AS
--Decalre variables
DECLARE @ALLCLASSES varchar(1000)
DECLARE @CNAME varchar (100)
--Create temp objects
CREATE TABLE #TempCourses
(
CNAME varchar(100),
STUDENT varchar (100),
FLAG int
)
--Populate the Temp table
INSERT #TempCourses
SELECT tClasses.CNAME, tStudents.STUDENT,0
FROM tCourses LEFT OUTER JOIN tClasses ON tCourses.CID = tClasses.CID
LEFT OUTER JOIN tStudents ON tCourses.SID = tStudents.SID
WHERE tCourses.SID = @SID
-- Set variables
SET @ALLCLASSES = ''
-- Get first course
SET @CNAME = (SELECT TOP 1 CNAME FROM #TempCourses WHERE FLAG=0)
-- Here I go Loopy
WHILE NOT (@CNAME IS NULL)
BEGIN
SET @ALLCLASSES = @ALLCLASSES + ', ' + @CNAME
UPDATE #TempCourses SET FLAG=1 WHERE CNAME LIKE @CNAME
SET @CNAME = (SELECT TOP 1 CNAME FROM #TempCourses WHERE FLAG=0)
END
--Clean up the Output variable
SET @ALLCLASSES = SUBSTRING(@ALLCLASSES,2,LEN(@ALLCLASSES))
-- ##OUTPUT
SELECT TOP 1 @ALLCLASSES AS ALLCOURSES FROM #TempCourses
--Drop temp objects
DROP TABLE #TempCourses
GO


Logicalman