SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy avoiding redundant data in result sets - ms sql 2000

    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:
    Code:
    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:
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by r937 View Post
    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
    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!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would combine several queries in a stored procedure only if i knew how to write the stored procedure to return multiple result sets

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    "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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    query a
    query b
    query c
    query d
    query e
    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).

    Thanks.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks -
    oh I wish I was still using Coldfusion, but I'm in ASP now. Is there a better forum on here for that?
    Thanks again

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, we have an asp forum here, also a dot net forum

    start a new thread, "multiple query result sets" or something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •