buck1107 — 2010-02-04T11:40:25-05:00 — #1
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
r937 — 2010-02-04T12:35:21-05:00 — #2
the "duplication" you speak of is not a problem, it is a feature!!!
first, please realize that you can have only one one-to-many relationship involved in a join query -- if there is more than one, you get multiplicity of results, or "cross join effects"
so there is really only one instance of duplication, and "looping" over the result set in your application is actually quite straightforward
buck1107 — 2010-02-04T15:05:30-05:00 — #3
Thanks for your help - much appreciated!
Actually, I am getting "cross join effects" because the duplication extends beyond what I gave in my example above. Thanks for the rule of thumb on only one one-to-many relationship. I'm very new to this and have been wondering what the term what to describe the huge results I was seeing!
As I will probably need to use separate queries, is there a way that I can incorporate those separate queries into the same stored procedure to help keep maintenance simpler?
Many thanks again for your help!
r937 — 2010-02-04T17:39:28-05:00 — #4
i would combine several queries in a stored procedure only if i knew how to write the stored procedure to return multiple result sets
buck1107 — 2010-02-05T17:14:03-05:00 — #5
I think, upon further reflection, perhaps
if I use separate queries (to avoid cross join effects) to find matching records in the 5 tables, then I can bring back a link in the result page with the student ID. That then can display the student details.
However, if I wanted to display other data in the link (from another table), I would need to get the other table's data. If, for example, I filled out a search box for 'student test date' and then in the resulting link, I wanted to have the student name (which is in a different table than the test date), I would need some sort of join between the queries...how would this be possible?
I wonder if the initial db query could be achieved with 'nested queries' within a main query (more set-based)?
Thanks for any help again. Much appreciated!
from the search page
r937 — 2010-02-05T17:23:11-05:00 — #6
"nested queries within a main query" is actually one way of describing a result set which has cross join effects
you have to have separate result sets, which means separate queries, which is what led to my remark in post #4
anyhow, what you do with those separate result sets is up to your application logic (the bit about the links i did not understand)
buck1107 — 2010-02-05T17:36:40-05:00 — #7
Thanks - in using separate queries, in order to use all of the result sets at the same time, would I need to put them into another (temporary) table, or would they each still be in scope?
If I had:
and then desired to use data from any/all of the queried tables in the application, would that data then be available to the application?
(The 'links' thing was just referencing a type of resultant display in the interface - URL with querystring, etc).
r937 — 2010-02-05T17:55:49-05:00 — #8
sorry, that is more properly a question for your application language
i know how to do it in coldfusion, but you're probably using php...
buck1107 — 2010-02-05T17:57:51-05:00 — #9
oh I wish I was still using Coldfusion, but I'm in ASP now. Is there a better forum on here for that?
r937 — 2010-02-05T18:11:24-05:00 — #10
yes, we have an asp forum here, also a dot net forum
start a new thread, "multiple query result sets" or something