Results 1 to 10 of 10
Feb 4, 2010, 10:40 #1
- Join Date
- Mar 2009
- 0 Post(s)
- 0 Thread(s)
avoiding redundant data in result sets - ms sql 2000
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
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