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]
Set NoCount ON
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Declare @Year2 AS int
Set @SQLQuery = 'SELECT
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.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.[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
dbo.zz_EXT_ED_c ON zz_BIO_c.[ID] = zz_EXT_ED_c.[ID]
dbo.zz_PCAT_c ON zz_BIO_c.[ID] = zz_PCAT_c.[ID]
dbo.zz_GRADES_c ON zz_BIO_c.[ID] = zz_GRADES_c.[ID]
dbo.zz_EXT_CRS_Combined_c ON zz_BIO_c.[ID] = zz_EXT_CRS_Combined_c.[ID]
WHERE (1=1) '
If @Year2 Is Not Null
Set @SQLQuery = @SQLQuery + ' And (zz_EXT_CRS_Combined_c.[Year] = @Year2) '
Set @ParamDefinition = '@Year2 Int'
Execute dbo.sp_Executesql @SQLQuery, @ParamDefinition, @Year2
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF