Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2008
    Posts
    37

    Unanswered: Two inner joins or more selects

    Can someone tell me if i am better off using two inner joins in this situation?
    I am assigning a student to a tutor, by picking the first tutor with free space (got by counting the number of students they already have in the students table) and also if the tutor has a preference for the students course (gotten from a preference table). I am using the code below at the moment (which doesnt actually work ive a syntax error)

    course = rs("STU_COURSE_CODE")
    strsql2 = "SELECT TOP 1 tblTutor.TU_CODE, tblPreference.TU_COURSE, tblPreference.TU_PREF " _
    & "FROM tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE " _
    & "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_COURSE, tblStudents.STU_TU_CODE, tblPreference.TU_COURSE, tblPreference.TU_PREF " _
    & "HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND (tblPreference.[TU_COURSE] = '" & course & "') AND (tblPreference.[TU_PREF] = true) )"

    Set rs2 = db.OpenRecordset(strsql2)

    If rs2.RecordCount <> 0 Then
    rs.Edit
    rs!STU_TU_CODE = rs2!TU_CODE
    rs.Update
    End If


    .MoveNext

    Loop

    P.s: Happy Valentines Day!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Whether it's better or not I couldn't tell you but when I had to do something similar (except I was dealing with records in the millions), I broke the routine into separate parts. Again, I was dealing with millions of records and opening 1 recordset with joined tables was not as quick verses opening 2 or 3 recordsets without joined tables. I would open 1 recordset based on criteria and use the results to construct the sql statement to open the 2nd recordset where I ultimately ended up with the same results as if I had opened 1 recordset with joined tables.

    I'm not sure if this technique is applicable in your situation given that you're probably not dealing with millions of records but it's something you can keep in mind. I also created temp tables in certain situations, creating a temp table on the first results and using that to get the 2nd results.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    isn't that a problem for A's query optimiser rather than for you?

    if you can write it as a single query, leave the optimiser to work out an execution plan. even if the optimiser comes up with a second-best plan, you wont notice the difference until you get to pkstormy's millions of records and you will have simplified your life.

    i don't see the syntax error at first glance, unless course is numeric (in which case drop the single quotes surrounding it)

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The query optimiser is only as good as its designed, and sometimes optimisers do make very odd decisions.

    All the books, references etc suggest that you should make sure your SQL is as "good" as possible, filtering out the redundant data as early as possible aggregating as quickly as possible. trying to reduce the number of rows per query. some of those are tasks beyond the optimisers field of competence in my books.

    besides which its no bad idea to look at queries (revist them mebbe once the system has got 'serious' data in it, just to check the performance isn't degraded by real life data).

    Its amazing how much users will put up with, but in my books we shouldn't be satisfied with the users happy with that, we should be making sure our apps are making best use of resources as possible. If a frequently used query kills the system then it needs revisiting. if it runs over night its not a problem, but if its during normal office hours then its a problem.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There are some definate issues with the query. I have re-writen yours to be clearer below:
    Code:
    SELECT TOP 1
           tblTutor.TU_CODE
         , tblPreference.TU_COURSE
         , tblPreference.TU_PREF 
    FROM   tblTutor
     LEFT
      JOIN tblStudents
        ON tblTutor.TU_CODE = tblStudents.STU_TU_CODE 
    GROUP
        BY tblTutor.TU_CODE
         , tblTutor.TU_CHAMBER_SIZE
         , tblTutor.TU_DP_NO
         , tblTutor.TU_COURSE
         , tblStudents.STU_TU_CODE
         , tblPreference.TU_COURSE
         , tblPreference.TU_PREF 
    HAVING TU_CHAMBER_SIZE > Count(tblStudents.STU_TU_CODE)
    AND    tblPreference.TU_COURSE = 'someValue'
    AND    tblPreference.TU_PREF = true
    First thing (I'm shocked no-one else picked up on it!) is that you CANNOT use TOP without an ORDER BY clause.
    Furthermore; your HAVING clause has things that need to be in the WHERE clause (having is for filtering your results after they have been grouped).
    Next off, you have missed out which table the field tu_chamber_size comes from. I bet the optimzer can guess; but let's not give it any more work to do than it has to!
    Code:
    SELECT TOP 1
           tblTutor.TU_CODE
         , tblPreference.TU_COURSE
         , tblPreference.TU_PREF 
    FROM   tblTutor
     LEFT
      JOIN tblStudents
        ON tblTutor.TU_CODE = tblStudents.STU_TU_CODE
    WHERE  tblPreference.TU_COURSE = 'someValue'
    AND    tblPreference.TU_PREF = true
    GROUP
        BY tblTutor.TU_CODE
         , tblTutor.TU_CHAMBER_SIZE
         , tblTutor.TU_DP_NO
         , tblTutor.TU_COURSE
         , tblStudents.STU_TU_CODE
         , tblPreference.TU_COURSE
         , tblPreference.TU_PREF 
    HAVING <someTable>.TU_CHAMBER_SIZE > Count(tblStudents.STU_TU_CODE)
    ORDER
        BY <someField(s)>
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    First thing (I'm shocked no-one else picked up on it!) is that you CANNOT use TOP without an ORDER BY clause.
    actually, you can

    it just doesn't make any sense to


    by the way, george, your code formatting is gorgeous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I find it easier to just say ou can't, but you are quite corect Rudy..... It works, but not always as you'd expect.

    And thanks Rudy; I actually picked some of the ideas from your formatting. Consistency has always ben the key for me and I just developed my own... style?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    consistency counts, yup

    okay, now that that's out of the way, do you realize that you make reference to three different tables in the query, but mention only two of them in the FROM clause?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    ...by the way, george, your code formatting is gorgeous

    ...or should that be "georgeous"

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    okay, now that that's out of the way, do you realize that you make reference to three different tables in the query, but mention only two of them in the FROM clause?

    Ha, I missed that one, good catch Rudy!
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mea culpa: this is a modified clone of my SQL (the original worked!).

    TOP 1 without the ORDER BY is functional in the context: any one (but only one) matching record is needed (biggest, smallest or anywhere in between doesn't matter).

    is FIRST (still without ORDER BY) better?

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The concern here is how you can possibly define which item is the top of a resultset. As we all [should] know the physical order of data in a database has no meaning, therefore if we don't explicitly state what order we want the resultset to be; we can never be sure what will be top, or indeed first.

    As you have observed, you will get a result. Any resultset that returns 1+ rows has a top (first, bottom, last), but if you want that to have any meaning, you have to use an ORDER BY clause.
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by izyrider
    any one (but only one) matching record is needed (biggest, smallest or anywhere in between doesn't matter).
    so use MAX()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    George:
    the whole point is that this exercise simply doesn't care which matching record is returned - any one of the set of matching records will do - rank has zero significance, the one-ness and matching-ness are all that count.

    Rudy:
    i will experiment with MAX()
    is there any fundamental reason what it should be "better" than TOP 1 or than FIRST (...without ORDER BY)
    ...given the all-important proviso that ranking has no significance in this exercise.
    maybe my entire query is crap: gameplan is to return ANY ONE matching record (of potentially many matching records) that has
    Count(someField) < literalXX
    someKey = literalYY
    do you have a slicker idea for achieving this?

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fundamental reason? glad you asked

    TOP and FIRST are non-standard sql

    as for slicker, i'm still wrestling with the idea of a situation where picking "any one" out of a one-to-many relationship is perfectly reasonable, where the latest or the best or the most noteworthy or the earliest or the biggest or the longest or the most popular or the skinniest or the largest or the funniest or the most frequent or the quickest wouldn't be a more natural choice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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