Hi,
I have 5 tables in which the first table has a one-to-many relationship with the other four tables. These tables are the backend of an ASP (classic) search application.
The resultsets of a query performed on these tables contains many rows for each student, with only a few columns being unique amongst rows. For example, here is what the rows are like:
Bill Smith,Univ of Co,BS,Cell Biology,A
Bill Smith,Univ of Co,BS,General Zoology,B
Bill Smith,Univ of Co,BS,Conv French I,A
(only there are many more total columns and rows, due to the inner join in the query)
My question is: is there a more efficient way of getting all data associated with a certain student id whose data spans across 5 tables? This will require quite a bit of looping when the search results return from the db to the ASP application, due to the duplication that exists in the rows of the result sets.
My mind is mush! lol 
Thanks for any help. 
here’s the query in my stored procedure:
CREATE PROCEDURE [dbo].[GetGrades3]
AS
Set NoCount ON
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Declare @Year2 AS int
Set @SQLQuery = 'SELECT
top 1000
zz_BIO_c.ID as [StudentID], zz_BIO_c.[Admit Term] as AdmitTerm, zz_BIO_c.name as [StudentName], zz_BIO_c.Birthdate,
zz_BIO_c.[Mar Status] as MarStatus, zz_BIO_c.Sex,
zz_EXT_ED_c.[Admit Term] as ExtEdAdmitTerm, zz_EXT_ED_c.[Org ID] as OrgID, zz_EXT_ED_c.Descr as Institution, zz_EXT_ED_c.Degree,
zz_EXT_ED_c.Descr1 as DegreeFull, zz_EXT_ED_c.OU_TTL_GPA as ExternalGPA,
zz_PCAT_c.[Admit Term] as PCATAdmitTerm, zz_PCAT_c.[Test ID], zz_PCAT_c.Component, zz_PCAT_c.Score, zz_PCAT_c.[Test Dt],
zz_GRADES_c.[Admit Term] as GradesAdmitTerm, zz_GRADES_c.[Strt Level], zz_GRADES_c.Term, zz_GRADES_c.Grade,
zz_GRADES_c.[Grade Base],
zz_GRADES_c.Subject, zz_GRADES_c.[Catalog], zz_GRADES_c.Descr,
zz_EXT_CRS_Combined_c.ID, zz_EXT_CRS_Combined_c.[Admit Term] as zz_EXT_CRS_Combined_AdmitTerm, zz_EXT_CRS_Combined_c.[Org ID] as zz_EXT_CRS_Combined_OrgID,
zz_EXT_CRS_Combined_c.[Ext Term],
zz_EXT_CRS_Combined_c.[Year], zz_EXT_CRS_Combined_c.Descr as zz_EXT_CRS_Combined_Descr, zz_EXT_CRS_Combined_c.Subject as zz_EXT_CRS_Combined_Subject,
zz_EXT_CRS_Combined_c.Grade as zz_EXT_CRS_Combined_Grade
FROM dbo.zz_BIO_c
INNER JOIN
dbo.zz_EXT_ED_c ON zz_BIO_c.[ID] = zz_EXT_ED_c.[ID]
INNER JOIN
dbo.zz_PCAT_c ON zz_BIO_c.[ID] = zz_PCAT_c.[ID]
INNER JOIN
dbo.zz_GRADES_c ON zz_BIO_c.[ID] = zz_GRADES_c.[ID]
INNER JOIN
dbo.zz_EXT_CRS_Combined_c ON zz_BIO_c.[ID] = zz_EXT_CRS_Combined_c.[ID]
WHERE (1=1) '
set @Year2=1997
If @Year2 Is Not Null
begin
Set @SQLQuery = @SQLQuery + ' And (zz_EXT_CRS_Combined_c.[Year] = @Year2) '
end
Set @ParamDefinition = '@Year2 Int'
Execute dbo.sp_Executesql @SQLQuery, @ParamDefinition, @Year2
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)
ErrorHandler:
Return(@@ERROR)
GO
