Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Combining statements

    Hi guys, can you explain to me how I can combine these statements into one please?

    They all query the same table with different criteria so I guess it might be possible?

    Thank you

    Code:
    'find how many passes there are in this section
    set passRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 1 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
    strPass = passRS("passNo")
    passRS.CLOSE()
    SET passRS = NOTHING
    'How many did student get?
    set passTotalRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 1 AND critieria_section_no = "&strSectionRS)
    strTotalPass = passTotalRS("passNo")
    passTotalRS.CLOSE
    SET passTotalRS = NOTHING
    'find how many merits there are in this section
    set meritRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 2 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
    strMerit = meritRS("meritNo")
    meritRS.CLOSE
    SET meritRS = NOTHING
    'How many did student get?
    set meritTotalRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 2 AND critieria_section_no = "&strSectionRS)
    strTotalMerit = meritTotalRS("meritNo")
    meritTotalRS.CLOSE
    SET meritTotalRS = NOTHING
    'find how many distinctions there are in this section
    set distinctRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 3 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
    strDistinct = distinctRS("distinctNo")
    distinctRS.CLOSE
    SET distinctRS = NOTHING
    'How many did student get?
    set distinctTotalRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 3 AND critieria_section_no = "&strSectionRS)
    strTotalDistinct = distinctTotalRS("distinctNo")
    distinctTotalRS.CLOSE
    SET distinctTotalRS = NOTHING

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Probably still want to do two queries, since there are two really very separate cases.

    But your DB appears badly designed. Why would you *EVER* create separate tables for different schools? Why not one table for all schools with just a field to say *which* school? If there were some kind of security question involved, it might make sense, except *THEN* you should be using separate databases with separate logins/passwords for each database. It makes no sense at all to have separate tables in a single database from any perspective I can think of.

    Anyway:

    There are two basic ways: Get all 3 criteria in one record or get 3 records, one with each criteria level.

    So... first, all 3 in one record:

    Code:
    marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
             & "           SUM(IF(criteria_level,2,1,0) AS meritNo, " _
             & "          SUM(IF(criteria_level,2,1,0) AS distinctNo " _
             & " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
             & " WHERE marks_criteria_id = criteria_id " _
             & " AND marks_username = '"&userRS("user_username")&"' " _
             & " AND critieria_section_no = "&strSectionRS
    Set RS = conn.Execute( marksSql )
    passNo = RS("passNo")
    meritNo = RS("meritNo")
    distnctNo = RS("distincNo")
    RS.Close
    
    marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
             & "           SUM(IF(criteria_level,2,1,0) AS meritNo, " _
             & "          SUM(IF(criteria_level,2,1,0) AS distinctNo " _
             & " FROM z_"&strSchool&"_criteria " _
             & " WHERE critieria_section_no = "&strSectionRS
    Set RS = conn.Execute( marksSql )
    totalPassNo = RS("passNo")
    totalMeritNo = RS("meritNo")
    totalDistnctNo = RS("distincNo")
    RS.Close
    (I just could *NOT* bring myself to code that as strPassNo = RS("passNo") when *CLEARLY* the value of strPassNo would *NOT* be as string! It *WILL* be a number. A count (or sum) *MUST* be a number? Why would you ever named the variable str when it holds a number?

    (Of course, I happen to be one of those who think that "Hungarian Notation" is an abomination. I have never opted to use prefixes on my variables such as "str" and "int" and "obj". Why MicroSLOP ever started that I will never know, but it's nice to know that even the Slop Shop has finally abandoned them, for the most part.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    generaltomfool (12-17-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Then the other way: 3 records per query.
    Code:
    marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _
             & " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
             & " WHERE marks_criteria_id = criteria_id " _
             & " AND marks_username = '"&userRS("user_username")&"' " _
             & " AND critieria_section_no = "&strSectionRS _
             & " GROUP BY criteria_level ORDER BY criteria_level
    Set RS = conn.Execute( marksSql )
    passNo = RS("criNo")
    RS.MoveNext
    meritNo = RS("criNo")
    RS.MoveNext
    distnctNo = RS("criNo")
    RS.Close
    
    marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _
             & " FROM z_"&strSchool&"_criteria " _
             & " WHERE critieria_section_no = "&strSectionRS _
             & " GROUP BY criteria_level ORDER BY criteria_level
    Set RS = conn.Execute( marksSql )
    totalPassNo = RS("criNo")
    RS.MoveNext
    totalMeritNo = RS("critNo")
    RS.MoveNext
    totalDistnctNo = RS("criNo")
    RS.Close
    Ugh...just noticed.

    You even have
    Code:
             & " WHERE critieria_section_no = "&strSectionRS _
    and again, strSectionRS is clearly a number. So why the "str" prefix?

    If you are going to insist on using ugly Hungarian Notation, you should at least use it correctly: If a variable holds a number, it should never have a str prefix.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    By the way, since this *IS* the MYSQL forum, my answers above are for MySQL queries.

    If you are using Access, then you simply change IF to IIF in my first post.

    If you are using SQL Server, you would have to use CASE WHEN in place of IF

    The answers in the second post should work for most any DB.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you - I think I understand what you have done and will experiment with your methods.

    And WOW - no holds barred on the corrections - I will take them on board too, why 'Hungarian'? I am self taught (ouch!) and happily take these tips on board.

    Thank you for taking the time to help me!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    LOL! Teach me to assume!

    I just assumed you had been reading MS recommended practices from 10 or more years ago. MS was very strongly into the so-called Hungarian Notation until .NET came along, at which time they eased up quite a bit.

    As for why "Hungarian": http://en.wikipedia.org/wiki/Hungarian_notation
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    generaltomfool (12-20-2012)

  • #7
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I've implemented your code, it has HALVED page load time - thank you very much!
    Can you recommend a site I can look at this level of tutorials (or dare I say a book I could read...?) I can use to get my knowledge up please?

    Thanks again,


    Jon

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    I hate to disappoint you, but I've never found a book that teaches SQL tricks.

    I've either learned them all on my own or seen somebody use them first. Or I've seen something and then tried to improve it.

    My primary resource for learning MySQL (I had previously used SQL Server and Access SQL) was just the MySQL documentation:
    http://dev.mysql.com/doc/refman/5.5/en/index.html

    I actually came up with the SUM(IF(...),1,0) trick on my own maybe 12 years ago, when I was using Access (Access uses IIF instead of IF, but otherwise the same). Only to then find out a year or so later that it's a standard technique in SQL Server (where you have to use CASE WHEN instead off IIF, but concepts are the same). Anyway, when I started using MySQL heavily (starting in 2009) it was an easy transition.

    MySQL has some impressively powerful features. Partitioned tables are a wonderful example. I used them to increase the performance of removing old records by a factor of maybe 100 or more. But it also has some strange limitiations. Its use of indexes, for example, is really really poor compared to SQL Server. (SQL Server will do index-only JOINs to get the number of records fetched way way down before it actually has to go get the records, per se. MySQL won't do that, at all. It's a huge limitation on performance.)

    Anyway, what it comes down to is: practice, practice, practice. I actually *PLAY* with MySQL almost every day. (Well, I play with HTML and JavaScript every day, too, come to think of it.) That is, I purposely try things that I don't actually need as part of my work for that day, just to see if they work better. Then when I have to actually implement something "on the job", I have more weapons in my coding arsenal to choose from.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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